1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    is_int,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42    Q = t.TypeVar("Q", bound="Query")
  43
  44
  45class _Expression(type):
  46    def __new__(cls, clsname, bases, attrs):
  47        klass = super().__new__(cls, clsname, bases, attrs)
  48
  49        # When an Expression class is created, its key is automatically set to be
  50        # the lowercase version of the class' name.
  51        klass.key = clsname.lower()
  52
  53        # This is so that docstrings are not inherited in pdoc
  54        klass.__doc__ = klass.__doc__ or ""
  55
  56        return klass
  57
  58
  59SQLGLOT_META = "sqlglot.meta"
  60TABLE_PARTS = ("this", "db", "catalog")
  61COLUMN_PARTS = ("this", "table", "db", "catalog")
  62
  63
  64class Expression(metaclass=_Expression):
  65    """
  66    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  67    context, such as its child expressions, their names (arg keys), and whether a given child expression
  68    is optional or not.
  69
  70    Attributes:
  71        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  72            and representing expressions as strings.
  73        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  74            arg keys to booleans that indicate whether the corresponding args are optional.
  75        parent: a reference to the parent expression (or None, in case of root expressions).
  76        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  77            uses to refer to it.
  78        index: the index of an expression if it is inside of a list argument in its parent.
  79        comments: a list of comments that are associated with a given expression. This is used in
  80            order to preserve comments when transpiling SQL code.
  81        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  82            optimizer, in order to enable some transformations that require type information.
  83        meta: a dictionary that can be used to store useful metadata for a given expression.
  84
  85    Example:
  86        >>> class Foo(Expression):
  87        ...     arg_types = {"this": True, "expression": False}
  88
  89        The above definition informs us that Foo is an Expression that requires an argument called
  90        "this" and may also optionally receive an argument called "expression".
  91
  92    Args:
  93        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  94    """
  95
  96    key = "expression"
  97    arg_types = {"this": True}
  98    __slots__ = ("args", "parent", "arg_key", "index", "comments", "_type", "_meta", "_hash")
  99
 100    def __init__(self, **args: t.Any):
 101        self.args: t.Dict[str, t.Any] = args
 102        self.parent: t.Optional[Expression] = None
 103        self.arg_key: t.Optional[str] = None
 104        self.index: t.Optional[int] = None
 105        self.comments: t.Optional[t.List[str]] = None
 106        self._type: t.Optional[DataType] = None
 107        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 108        self._hash: t.Optional[int] = None
 109
 110        for arg_key, value in self.args.items():
 111            self._set_parent(arg_key, value)
 112
 113    def __eq__(self, other) -> bool:
 114        return type(self) is type(other) and hash(self) == hash(other)
 115
 116    @property
 117    def hashable_args(self) -> t.Any:
 118        return frozenset(
 119            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 120            for k, v in self.args.items()
 121            if not (v is None or v is False or (type(v) is list and not v))
 122        )
 123
 124    def __hash__(self) -> int:
 125        if self._hash is not None:
 126            return self._hash
 127
 128        return hash((self.__class__, self.hashable_args))
 129
 130    @property
 131    def this(self) -> t.Any:
 132        """
 133        Retrieves the argument with key "this".
 134        """
 135        return self.args.get("this")
 136
 137    @property
 138    def expression(self) -> t.Any:
 139        """
 140        Retrieves the argument with key "expression".
 141        """
 142        return self.args.get("expression")
 143
 144    @property
 145    def expressions(self) -> t.List[t.Any]:
 146        """
 147        Retrieves the argument with key "expressions".
 148        """
 149        return self.args.get("expressions") or []
 150
 151    def text(self, key) -> str:
 152        """
 153        Returns a textual representation of the argument corresponding to "key". This can only be used
 154        for args that are strings or leaf Expression instances, such as identifiers and literals.
 155        """
 156        field = self.args.get(key)
 157        if isinstance(field, str):
 158            return field
 159        if isinstance(field, (Identifier, Literal, Var)):
 160            return field.this
 161        if isinstance(field, (Star, Null)):
 162            return field.name
 163        return ""
 164
 165    @property
 166    def is_string(self) -> bool:
 167        """
 168        Checks whether a Literal expression is a string.
 169        """
 170        return isinstance(self, Literal) and self.args["is_string"]
 171
 172    @property
 173    def is_number(self) -> bool:
 174        """
 175        Checks whether a Literal expression is a number.
 176        """
 177        return isinstance(self, Literal) and not self.args["is_string"]
 178
 179    @property
 180    def is_negative(self) -> bool:
 181        """
 182        Checks whether an expression is negative.
 183
 184        Handles both exp.Neg and Literal numbers with "-" which come from optimizer.simplify.
 185        """
 186        return isinstance(self, Neg) or (self.is_number and self.this.startswith("-"))
 187
 188    @property
 189    def is_int(self) -> bool:
 190        """
 191        Checks whether a Literal expression is an integer.
 192        """
 193        return self.is_number and is_int(self.name)
 194
 195    @property
 196    def is_star(self) -> bool:
 197        """Checks whether an expression is a star."""
 198        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 199
 200    @property
 201    def alias(self) -> str:
 202        """
 203        Returns the alias of the expression, or an empty string if it's not aliased.
 204        """
 205        if isinstance(self.args.get("alias"), TableAlias):
 206            return self.args["alias"].name
 207        return self.text("alias")
 208
 209    @property
 210    def alias_column_names(self) -> t.List[str]:
 211        table_alias = self.args.get("alias")
 212        if not table_alias:
 213            return []
 214        return [c.name for c in table_alias.args.get("columns") or []]
 215
 216    @property
 217    def name(self) -> str:
 218        return self.text("this")
 219
 220    @property
 221    def alias_or_name(self) -> str:
 222        return self.alias or self.name
 223
 224    @property
 225    def output_name(self) -> str:
 226        """
 227        Name of the output column if this expression is a selection.
 228
 229        If the Expression has no output name, an empty string is returned.
 230
 231        Example:
 232            >>> from sqlglot import parse_one
 233            >>> parse_one("SELECT a").expressions[0].output_name
 234            'a'
 235            >>> parse_one("SELECT b AS c").expressions[0].output_name
 236            'c'
 237            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 238            ''
 239        """
 240        return ""
 241
 242    @property
 243    def type(self) -> t.Optional[DataType]:
 244        return self._type
 245
 246    @type.setter
 247    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 248        if dtype and not isinstance(dtype, DataType):
 249            dtype = DataType.build(dtype)
 250        self._type = dtype  # type: ignore
 251
 252    def is_type(self, *dtypes) -> bool:
 253        return self.type is not None and self.type.is_type(*dtypes)
 254
 255    def is_leaf(self) -> bool:
 256        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 257
 258    @property
 259    def meta(self) -> t.Dict[str, t.Any]:
 260        if self._meta is None:
 261            self._meta = {}
 262        return self._meta
 263
 264    def __deepcopy__(self, memo):
 265        root = self.__class__()
 266        stack = [(self, root)]
 267
 268        while stack:
 269            node, copy = stack.pop()
 270
 271            if node.comments is not None:
 272                copy.comments = deepcopy(node.comments)
 273            if node._type is not None:
 274                copy._type = deepcopy(node._type)
 275            if node._meta is not None:
 276                copy._meta = deepcopy(node._meta)
 277            if node._hash is not None:
 278                copy._hash = node._hash
 279
 280            for k, vs in node.args.items():
 281                if hasattr(vs, "parent"):
 282                    stack.append((vs, vs.__class__()))
 283                    copy.set(k, stack[-1][-1])
 284                elif type(vs) is list:
 285                    copy.args[k] = []
 286
 287                    for v in vs:
 288                        if hasattr(v, "parent"):
 289                            stack.append((v, v.__class__()))
 290                            copy.append(k, stack[-1][-1])
 291                        else:
 292                            copy.append(k, v)
 293                else:
 294                    copy.args[k] = vs
 295
 296        return root
 297
 298    def copy(self):
 299        """
 300        Returns a deep copy of the expression.
 301        """
 302        return deepcopy(self)
 303
 304    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 305        if self.comments is None:
 306            self.comments = []
 307        if comments:
 308            for comment in comments:
 309                _, *meta = comment.split(SQLGLOT_META)
 310                if meta:
 311                    for kv in "".join(meta).split(","):
 312                        k, *v = kv.split("=")
 313                        value = v[0].strip() if v else True
 314                        self.meta[k.strip()] = value
 315                self.comments.append(comment)
 316
 317    def append(self, arg_key: str, value: t.Any) -> None:
 318        """
 319        Appends value to arg_key if it's a list or sets it as a new list.
 320
 321        Args:
 322            arg_key (str): name of the list expression arg
 323            value (Any): value to append to the list
 324        """
 325        if type(self.args.get(arg_key)) is not list:
 326            self.args[arg_key] = []
 327        self._set_parent(arg_key, value)
 328        values = self.args[arg_key]
 329        if hasattr(value, "parent"):
 330            value.index = len(values)
 331        values.append(value)
 332
 333    def set(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 334        """
 335        Sets arg_key to value.
 336
 337        Args:
 338            arg_key: name of the expression arg.
 339            value: value to set the arg to.
 340            index: if the arg is a list, this specifies what position to add the value in it.
 341        """
 342        if index is not None:
 343            expressions = self.args.get(arg_key) or []
 344
 345            if seq_get(expressions, index) is None:
 346                return
 347            if value is None:
 348                expressions.pop(index)
 349                for v in expressions[index:]:
 350                    v.index = v.index - 1
 351                return
 352
 353            if isinstance(value, list):
 354                expressions.pop(index)
 355                expressions[index:index] = value
 356            else:
 357                expressions[index] = value
 358
 359            value = expressions
 360        elif value is None:
 361            self.args.pop(arg_key, None)
 362            return
 363
 364        self.args[arg_key] = value
 365        self._set_parent(arg_key, value, index)
 366
 367    def _set_parent(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 368        if hasattr(value, "parent"):
 369            value.parent = self
 370            value.arg_key = arg_key
 371            value.index = index
 372        elif type(value) is list:
 373            for index, v in enumerate(value):
 374                if hasattr(v, "parent"):
 375                    v.parent = self
 376                    v.arg_key = arg_key
 377                    v.index = index
 378
 379    @property
 380    def depth(self) -> int:
 381        """
 382        Returns the depth of this tree.
 383        """
 384        if self.parent:
 385            return self.parent.depth + 1
 386        return 0
 387
 388    def iter_expressions(self, reverse: bool = False) -> t.Iterator[Expression]:
 389        """Yields the key and expression for all arguments, exploding list args."""
 390        # remove tuple when python 3.7 is deprecated
 391        for vs in reversed(tuple(self.args.values())) if reverse else self.args.values():
 392            if type(vs) is list:
 393                for v in reversed(vs) if reverse else vs:
 394                    if hasattr(v, "parent"):
 395                        yield v
 396            else:
 397                if hasattr(vs, "parent"):
 398                    yield vs
 399
 400    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 401        """
 402        Returns the first node in this tree which matches at least one of
 403        the specified types.
 404
 405        Args:
 406            expression_types: the expression type(s) to match.
 407            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 408
 409        Returns:
 410            The node which matches the criteria or None if no such node was found.
 411        """
 412        return next(self.find_all(*expression_types, bfs=bfs), None)
 413
 414    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 415        """
 416        Returns a generator object which visits all nodes in this tree and only
 417        yields those that match at least one of the specified expression types.
 418
 419        Args:
 420            expression_types: the expression type(s) to match.
 421            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 422
 423        Returns:
 424            The generator object.
 425        """
 426        for expression in self.walk(bfs=bfs):
 427            if isinstance(expression, expression_types):
 428                yield expression
 429
 430    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 431        """
 432        Returns a nearest parent matching expression_types.
 433
 434        Args:
 435            expression_types: the expression type(s) to match.
 436
 437        Returns:
 438            The parent node.
 439        """
 440        ancestor = self.parent
 441        while ancestor and not isinstance(ancestor, expression_types):
 442            ancestor = ancestor.parent
 443        return ancestor  # type: ignore
 444
 445    @property
 446    def parent_select(self) -> t.Optional[Select]:
 447        """
 448        Returns the parent select statement.
 449        """
 450        return self.find_ancestor(Select)
 451
 452    @property
 453    def same_parent(self) -> bool:
 454        """Returns if the parent is the same class as itself."""
 455        return type(self.parent) is self.__class__
 456
 457    def root(self) -> Expression:
 458        """
 459        Returns the root expression of this tree.
 460        """
 461        expression = self
 462        while expression.parent:
 463            expression = expression.parent
 464        return expression
 465
 466    def walk(
 467        self, bfs: bool = True, prune: t.Optional[t.Callable[[Expression], bool]] = None
 468    ) -> t.Iterator[Expression]:
 469        """
 470        Returns a generator object which visits all nodes in this tree.
 471
 472        Args:
 473            bfs: if set to True the BFS traversal order will be applied,
 474                otherwise the DFS traversal will be used instead.
 475            prune: callable that returns True if the generator should stop traversing
 476                this branch of the tree.
 477
 478        Returns:
 479            the generator object.
 480        """
 481        if bfs:
 482            yield from self.bfs(prune=prune)
 483        else:
 484            yield from self.dfs(prune=prune)
 485
 486    def dfs(
 487        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 488    ) -> t.Iterator[Expression]:
 489        """
 490        Returns a generator object which visits all nodes in this tree in
 491        the DFS (Depth-first) order.
 492
 493        Returns:
 494            The generator object.
 495        """
 496        stack = [self]
 497
 498        while stack:
 499            node = stack.pop()
 500
 501            yield node
 502
 503            if prune and prune(node):
 504                continue
 505
 506            for v in node.iter_expressions(reverse=True):
 507                stack.append(v)
 508
 509    def bfs(
 510        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 511    ) -> t.Iterator[Expression]:
 512        """
 513        Returns a generator object which visits all nodes in this tree in
 514        the BFS (Breadth-first) order.
 515
 516        Returns:
 517            The generator object.
 518        """
 519        queue = deque([self])
 520
 521        while queue:
 522            node = queue.popleft()
 523
 524            yield node
 525
 526            if prune and prune(node):
 527                continue
 528
 529            for v in node.iter_expressions():
 530                queue.append(v)
 531
 532    def unnest(self):
 533        """
 534        Returns the first non parenthesis child or self.
 535        """
 536        expression = self
 537        while type(expression) is Paren:
 538            expression = expression.this
 539        return expression
 540
 541    def unalias(self):
 542        """
 543        Returns the inner expression if this is an Alias.
 544        """
 545        if isinstance(self, Alias):
 546            return self.this
 547        return self
 548
 549    def unnest_operands(self):
 550        """
 551        Returns unnested operands as a tuple.
 552        """
 553        return tuple(arg.unnest() for arg in self.iter_expressions())
 554
 555    def flatten(self, unnest=True):
 556        """
 557        Returns a generator which yields child nodes whose parents are the same class.
 558
 559        A AND B AND C -> [A, B, C]
 560        """
 561        for node in self.dfs(prune=lambda n: n.parent and type(n) is not self.__class__):
 562            if type(node) is not self.__class__:
 563                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 564
 565    def __str__(self) -> str:
 566        return self.sql()
 567
 568    def __repr__(self) -> str:
 569        return _to_s(self)
 570
 571    def to_s(self) -> str:
 572        """
 573        Same as __repr__, but includes additional information which can be useful
 574        for debugging, like empty or missing args and the AST nodes' object IDs.
 575        """
 576        return _to_s(self, verbose=True)
 577
 578    def sql(self, dialect: DialectType = None, **opts) -> str:
 579        """
 580        Returns SQL string representation of this tree.
 581
 582        Args:
 583            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 584            opts: other `sqlglot.generator.Generator` options.
 585
 586        Returns:
 587            The SQL string.
 588        """
 589        from sqlglot.dialects import Dialect
 590
 591        return Dialect.get_or_raise(dialect).generate(self, **opts)
 592
 593    def transform(self, fun: t.Callable, *args: t.Any, copy: bool = True, **kwargs) -> Expression:
 594        """
 595        Visits all tree nodes (excluding already transformed ones)
 596        and applies the given transformation function to each node.
 597
 598        Args:
 599            fun: a function which takes a node as an argument and returns a
 600                new transformed node or the same node without modifications. If the function
 601                returns None, then the corresponding node will be removed from the syntax tree.
 602            copy: if set to True a new tree instance is constructed, otherwise the tree is
 603                modified in place.
 604
 605        Returns:
 606            The transformed tree.
 607        """
 608        root = None
 609        new_node = None
 610
 611        for node in (self.copy() if copy else self).dfs(prune=lambda n: n is not new_node):
 612            parent, arg_key, index = node.parent, node.arg_key, node.index
 613            new_node = fun(node, *args, **kwargs)
 614
 615            if not root:
 616                root = new_node
 617            elif new_node is not node:
 618                parent.set(arg_key, new_node, index)
 619
 620        assert root
 621        return root.assert_is(Expression)
 622
 623    @t.overload
 624    def replace(self, expression: E) -> E: ...
 625
 626    @t.overload
 627    def replace(self, expression: None) -> None: ...
 628
 629    def replace(self, expression):
 630        """
 631        Swap out this expression with a new expression.
 632
 633        For example::
 634
 635            >>> tree = Select().select("x").from_("tbl")
 636            >>> tree.find(Column).replace(column("y"))
 637            Column(
 638              this=Identifier(this=y, quoted=False))
 639            >>> tree.sql()
 640            'SELECT y FROM tbl'
 641
 642        Args:
 643            expression: new node
 644
 645        Returns:
 646            The new expression or expressions.
 647        """
 648        parent = self.parent
 649
 650        if not parent or parent is expression:
 651            return expression
 652
 653        key = self.arg_key
 654        value = parent.args.get(key)
 655
 656        if type(expression) is list and isinstance(value, Expression):
 657            # We are trying to replace an Expression with a list, so it's assumed that
 658            # the intention was to really replace the parent of this expression.
 659            value.parent.replace(expression)
 660        else:
 661            parent.set(key, expression, self.index)
 662
 663        if expression is not self:
 664            self.parent = None
 665            self.arg_key = None
 666            self.index = None
 667
 668        return expression
 669
 670    def pop(self: E) -> E:
 671        """
 672        Remove this expression from its AST.
 673
 674        Returns:
 675            The popped expression.
 676        """
 677        self.replace(None)
 678        return self
 679
 680    def assert_is(self, type_: t.Type[E]) -> E:
 681        """
 682        Assert that this `Expression` is an instance of `type_`.
 683
 684        If it is NOT an instance of `type_`, this raises an assertion error.
 685        Otherwise, this returns this expression.
 686
 687        Examples:
 688            This is useful for type security in chained expressions:
 689
 690            >>> import sqlglot
 691            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 692            'SELECT x, z FROM y'
 693        """
 694        if not isinstance(self, type_):
 695            raise AssertionError(f"{self} is not {type_}.")
 696        return self
 697
 698    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 699        """
 700        Checks if this expression is valid (e.g. all mandatory args are set).
 701
 702        Args:
 703            args: a sequence of values that were used to instantiate a Func expression. This is used
 704                to check that the provided arguments don't exceed the function argument limit.
 705
 706        Returns:
 707            A list of error messages for all possible errors that were found.
 708        """
 709        errors: t.List[str] = []
 710
 711        for k in self.args:
 712            if k not in self.arg_types:
 713                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 714        for k, mandatory in self.arg_types.items():
 715            v = self.args.get(k)
 716            if mandatory and (v is None or (isinstance(v, list) and not v)):
 717                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 718
 719        if (
 720            args
 721            and isinstance(self, Func)
 722            and len(args) > len(self.arg_types)
 723            and not self.is_var_len_args
 724        ):
 725            errors.append(
 726                f"The number of provided arguments ({len(args)}) is greater than "
 727                f"the maximum number of supported arguments ({len(self.arg_types)})"
 728            )
 729
 730        return errors
 731
 732    def dump(self):
 733        """
 734        Dump this Expression to a JSON-serializable dict.
 735        """
 736        from sqlglot.serde import dump
 737
 738        return dump(self)
 739
 740    @classmethod
 741    def load(cls, obj):
 742        """
 743        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 744        """
 745        from sqlglot.serde import load
 746
 747        return load(obj)
 748
 749    def and_(
 750        self,
 751        *expressions: t.Optional[ExpOrStr],
 752        dialect: DialectType = None,
 753        copy: bool = True,
 754        **opts,
 755    ) -> Condition:
 756        """
 757        AND this condition with one or multiple expressions.
 758
 759        Example:
 760            >>> condition("x=1").and_("y=1").sql()
 761            'x = 1 AND y = 1'
 762
 763        Args:
 764            *expressions: the SQL code strings to parse.
 765                If an `Expression` instance is passed, it will be used as-is.
 766            dialect: the dialect used to parse the input expression.
 767            copy: whether to copy the involved expressions (only applies to Expressions).
 768            opts: other options to use to parse the input expressions.
 769
 770        Returns:
 771            The new And condition.
 772        """
 773        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 774
 775    def or_(
 776        self,
 777        *expressions: t.Optional[ExpOrStr],
 778        dialect: DialectType = None,
 779        copy: bool = True,
 780        **opts,
 781    ) -> Condition:
 782        """
 783        OR this condition with one or multiple expressions.
 784
 785        Example:
 786            >>> condition("x=1").or_("y=1").sql()
 787            'x = 1 OR y = 1'
 788
 789        Args:
 790            *expressions: the SQL code strings to parse.
 791                If an `Expression` instance is passed, it will be used as-is.
 792            dialect: the dialect used to parse the input expression.
 793            copy: whether to copy the involved expressions (only applies to Expressions).
 794            opts: other options to use to parse the input expressions.
 795
 796        Returns:
 797            The new Or condition.
 798        """
 799        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 800
 801    def not_(self, copy: bool = True):
 802        """
 803        Wrap this condition with NOT.
 804
 805        Example:
 806            >>> condition("x=1").not_().sql()
 807            'NOT x = 1'
 808
 809        Args:
 810            copy: whether to copy this object.
 811
 812        Returns:
 813            The new Not instance.
 814        """
 815        return not_(self, copy=copy)
 816
 817    def as_(
 818        self,
 819        alias: str | Identifier,
 820        quoted: t.Optional[bool] = None,
 821        dialect: DialectType = None,
 822        copy: bool = True,
 823        **opts,
 824    ) -> Alias:
 825        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 826
 827    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 828        this = self.copy()
 829        other = convert(other, copy=True)
 830        if not isinstance(this, klass) and not isinstance(other, klass):
 831            this = _wrap(this, Binary)
 832            other = _wrap(other, Binary)
 833        if reverse:
 834            return klass(this=other, expression=this)
 835        return klass(this=this, expression=other)
 836
 837    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 838        return Bracket(
 839            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 840        )
 841
 842    def __iter__(self) -> t.Iterator:
 843        if "expressions" in self.arg_types:
 844            return iter(self.args.get("expressions") or [])
 845        # We define this because __getitem__ converts Expression into an iterable, which is
 846        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 847        # See: https://peps.python.org/pep-0234/
 848        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 849
 850    def isin(
 851        self,
 852        *expressions: t.Any,
 853        query: t.Optional[ExpOrStr] = None,
 854        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 855        copy: bool = True,
 856        **opts,
 857    ) -> In:
 858        subquery = maybe_parse(query, copy=copy, **opts) if query else None
 859        if subquery and not isinstance(subquery, Subquery):
 860            subquery = subquery.subquery(copy=False)
 861
 862        return In(
 863            this=maybe_copy(self, copy),
 864            expressions=[convert(e, copy=copy) for e in expressions],
 865            query=subquery,
 866            unnest=(
 867                Unnest(
 868                    expressions=[
 869                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 870                        for e in ensure_list(unnest)
 871                    ]
 872                )
 873                if unnest
 874                else None
 875            ),
 876        )
 877
 878    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 879        return Between(
 880            this=maybe_copy(self, copy),
 881            low=convert(low, copy=copy, **opts),
 882            high=convert(high, copy=copy, **opts),
 883        )
 884
 885    def is_(self, other: ExpOrStr) -> Is:
 886        return self._binop(Is, other)
 887
 888    def like(self, other: ExpOrStr) -> Like:
 889        return self._binop(Like, other)
 890
 891    def ilike(self, other: ExpOrStr) -> ILike:
 892        return self._binop(ILike, other)
 893
 894    def eq(self, other: t.Any) -> EQ:
 895        return self._binop(EQ, other)
 896
 897    def neq(self, other: t.Any) -> NEQ:
 898        return self._binop(NEQ, other)
 899
 900    def rlike(self, other: ExpOrStr) -> RegexpLike:
 901        return self._binop(RegexpLike, other)
 902
 903    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 904        div = self._binop(Div, other)
 905        div.args["typed"] = typed
 906        div.args["safe"] = safe
 907        return div
 908
 909    def asc(self, nulls_first: bool = True) -> Ordered:
 910        return Ordered(this=self.copy(), nulls_first=nulls_first)
 911
 912    def desc(self, nulls_first: bool = False) -> Ordered:
 913        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 914
 915    def __lt__(self, other: t.Any) -> LT:
 916        return self._binop(LT, other)
 917
 918    def __le__(self, other: t.Any) -> LTE:
 919        return self._binop(LTE, other)
 920
 921    def __gt__(self, other: t.Any) -> GT:
 922        return self._binop(GT, other)
 923
 924    def __ge__(self, other: t.Any) -> GTE:
 925        return self._binop(GTE, other)
 926
 927    def __add__(self, other: t.Any) -> Add:
 928        return self._binop(Add, other)
 929
 930    def __radd__(self, other: t.Any) -> Add:
 931        return self._binop(Add, other, reverse=True)
 932
 933    def __sub__(self, other: t.Any) -> Sub:
 934        return self._binop(Sub, other)
 935
 936    def __rsub__(self, other: t.Any) -> Sub:
 937        return self._binop(Sub, other, reverse=True)
 938
 939    def __mul__(self, other: t.Any) -> Mul:
 940        return self._binop(Mul, other)
 941
 942    def __rmul__(self, other: t.Any) -> Mul:
 943        return self._binop(Mul, other, reverse=True)
 944
 945    def __truediv__(self, other: t.Any) -> Div:
 946        return self._binop(Div, other)
 947
 948    def __rtruediv__(self, other: t.Any) -> Div:
 949        return self._binop(Div, other, reverse=True)
 950
 951    def __floordiv__(self, other: t.Any) -> IntDiv:
 952        return self._binop(IntDiv, other)
 953
 954    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 955        return self._binop(IntDiv, other, reverse=True)
 956
 957    def __mod__(self, other: t.Any) -> Mod:
 958        return self._binop(Mod, other)
 959
 960    def __rmod__(self, other: t.Any) -> Mod:
 961        return self._binop(Mod, other, reverse=True)
 962
 963    def __pow__(self, other: t.Any) -> Pow:
 964        return self._binop(Pow, other)
 965
 966    def __rpow__(self, other: t.Any) -> Pow:
 967        return self._binop(Pow, other, reverse=True)
 968
 969    def __and__(self, other: t.Any) -> And:
 970        return self._binop(And, other)
 971
 972    def __rand__(self, other: t.Any) -> And:
 973        return self._binop(And, other, reverse=True)
 974
 975    def __or__(self, other: t.Any) -> Or:
 976        return self._binop(Or, other)
 977
 978    def __ror__(self, other: t.Any) -> Or:
 979        return self._binop(Or, other, reverse=True)
 980
 981    def __neg__(self) -> Neg:
 982        return Neg(this=_wrap(self.copy(), Binary))
 983
 984    def __invert__(self) -> Not:
 985        return not_(self.copy())
 986
 987
 988IntoType = t.Union[
 989    str,
 990    t.Type[Expression],
 991    t.Collection[t.Union[str, t.Type[Expression]]],
 992]
 993ExpOrStr = t.Union[str, Expression]
 994
 995
 996class Condition(Expression):
 997    """Logical conditions like x AND y, or simply x"""
 998
 999
1000class Predicate(Condition):
1001    """Relationships like x = y, x > 1, x >= y."""
1002
1003
1004class DerivedTable(Expression):
1005    @property
1006    def selects(self) -> t.List[Expression]:
1007        return self.this.selects if isinstance(self.this, Query) else []
1008
1009    @property
1010    def named_selects(self) -> t.List[str]:
1011        return [select.output_name for select in self.selects]
1012
1013
1014class Query(Expression):
1015    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
1016        """
1017        Returns a `Subquery` that wraps around this query.
1018
1019        Example:
1020            >>> subquery = Select().select("x").from_("tbl").subquery()
1021            >>> Select().select("x").from_(subquery).sql()
1022            'SELECT x FROM (SELECT x FROM tbl)'
1023
1024        Args:
1025            alias: an optional alias for the subquery.
1026            copy: if `False`, modify this expression instance in-place.
1027        """
1028        instance = maybe_copy(self, copy)
1029        if not isinstance(alias, Expression):
1030            alias = TableAlias(this=to_identifier(alias)) if alias else None
1031
1032        return Subquery(this=instance, alias=alias)
1033
1034    def limit(
1035        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1036    ) -> Q:
1037        """
1038        Adds a LIMIT clause to this query.
1039
1040        Example:
1041            >>> select("1").union(select("1")).limit(1).sql()
1042            'SELECT 1 UNION SELECT 1 LIMIT 1'
1043
1044        Args:
1045            expression: the SQL code string to parse.
1046                This can also be an integer.
1047                If a `Limit` instance is passed, it will be used as-is.
1048                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1049            dialect: the dialect used to parse the input expression.
1050            copy: if `False`, modify this expression instance in-place.
1051            opts: other options to use to parse the input expressions.
1052
1053        Returns:
1054            A limited Select expression.
1055        """
1056        return _apply_builder(
1057            expression=expression,
1058            instance=self,
1059            arg="limit",
1060            into=Limit,
1061            prefix="LIMIT",
1062            dialect=dialect,
1063            copy=copy,
1064            into_arg="expression",
1065            **opts,
1066        )
1067
1068    def offset(
1069        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1070    ) -> Q:
1071        """
1072        Set the OFFSET expression.
1073
1074        Example:
1075            >>> Select().from_("tbl").select("x").offset(10).sql()
1076            'SELECT x FROM tbl OFFSET 10'
1077
1078        Args:
1079            expression: the SQL code string to parse.
1080                This can also be an integer.
1081                If a `Offset` instance is passed, this is used as-is.
1082                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
1083            dialect: the dialect used to parse the input expression.
1084            copy: if `False`, modify this expression instance in-place.
1085            opts: other options to use to parse the input expressions.
1086
1087        Returns:
1088            The modified Select expression.
1089        """
1090        return _apply_builder(
1091            expression=expression,
1092            instance=self,
1093            arg="offset",
1094            into=Offset,
1095            prefix="OFFSET",
1096            dialect=dialect,
1097            copy=copy,
1098            into_arg="expression",
1099            **opts,
1100        )
1101
1102    def order_by(
1103        self: Q,
1104        *expressions: t.Optional[ExpOrStr],
1105        append: bool = True,
1106        dialect: DialectType = None,
1107        copy: bool = True,
1108        **opts,
1109    ) -> Q:
1110        """
1111        Set the ORDER BY expression.
1112
1113        Example:
1114            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
1115            'SELECT x FROM tbl ORDER BY x DESC'
1116
1117        Args:
1118            *expressions: the SQL code strings to parse.
1119                If a `Group` instance is passed, this is used as-is.
1120                If another `Expression` instance is passed, it will be wrapped in a `Order`.
1121            append: if `True`, add to any existing expressions.
1122                Otherwise, this flattens all the `Order` expression into a single expression.
1123            dialect: the dialect used to parse the input expression.
1124            copy: if `False`, modify this expression instance in-place.
1125            opts: other options to use to parse the input expressions.
1126
1127        Returns:
1128            The modified Select expression.
1129        """
1130        return _apply_child_list_builder(
1131            *expressions,
1132            instance=self,
1133            arg="order",
1134            append=append,
1135            copy=copy,
1136            prefix="ORDER BY",
1137            into=Order,
1138            dialect=dialect,
1139            **opts,
1140        )
1141
1142    @property
1143    def ctes(self) -> t.List[CTE]:
1144        """Returns a list of all the CTEs attached to this query."""
1145        with_ = self.args.get("with")
1146        return with_.expressions if with_ else []
1147
1148    @property
1149    def selects(self) -> t.List[Expression]:
1150        """Returns the query's projections."""
1151        raise NotImplementedError("Query objects must implement `selects`")
1152
1153    @property
1154    def named_selects(self) -> t.List[str]:
1155        """Returns the output names of the query's projections."""
1156        raise NotImplementedError("Query objects must implement `named_selects`")
1157
1158    def select(
1159        self: Q,
1160        *expressions: t.Optional[ExpOrStr],
1161        append: bool = True,
1162        dialect: DialectType = None,
1163        copy: bool = True,
1164        **opts,
1165    ) -> Q:
1166        """
1167        Append to or set the SELECT expressions.
1168
1169        Example:
1170            >>> Select().select("x", "y").sql()
1171            'SELECT x, y'
1172
1173        Args:
1174            *expressions: the SQL code strings to parse.
1175                If an `Expression` instance is passed, it will be used as-is.
1176            append: if `True`, add to any existing expressions.
1177                Otherwise, this resets the expressions.
1178            dialect: the dialect used to parse the input expressions.
1179            copy: if `False`, modify this expression instance in-place.
1180            opts: other options to use to parse the input expressions.
1181
1182        Returns:
1183            The modified Query expression.
1184        """
1185        raise NotImplementedError("Query objects must implement `select`")
1186
1187    def with_(
1188        self: Q,
1189        alias: ExpOrStr,
1190        as_: ExpOrStr,
1191        recursive: t.Optional[bool] = None,
1192        append: bool = True,
1193        dialect: DialectType = None,
1194        copy: bool = True,
1195        **opts,
1196    ) -> Q:
1197        """
1198        Append to or set the common table expressions.
1199
1200        Example:
1201            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1202            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1203
1204        Args:
1205            alias: the SQL code string to parse as the table name.
1206                If an `Expression` instance is passed, this is used as-is.
1207            as_: the SQL code string to parse as the table expression.
1208                If an `Expression` instance is passed, it will be used as-is.
1209            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1210            append: if `True`, add to any existing expressions.
1211                Otherwise, this resets the expressions.
1212            dialect: the dialect used to parse the input expression.
1213            copy: if `False`, modify this expression instance in-place.
1214            opts: other options to use to parse the input expressions.
1215
1216        Returns:
1217            The modified expression.
1218        """
1219        return _apply_cte_builder(
1220            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1221        )
1222
1223    def union(
1224        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1225    ) -> Union:
1226        """
1227        Builds a UNION expression.
1228
1229        Example:
1230            >>> import sqlglot
1231            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1232            'SELECT * FROM foo UNION SELECT * FROM bla'
1233
1234        Args:
1235            expression: the SQL code string.
1236                If an `Expression` instance is passed, it will be used as-is.
1237            distinct: set the DISTINCT flag if and only if this is true.
1238            dialect: the dialect used to parse the input expression.
1239            opts: other options to use to parse the input expressions.
1240
1241        Returns:
1242            The new Union expression.
1243        """
1244        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1245
1246    def intersect(
1247        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1248    ) -> Intersect:
1249        """
1250        Builds an INTERSECT expression.
1251
1252        Example:
1253            >>> import sqlglot
1254            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1255            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1256
1257        Args:
1258            expression: the SQL code string.
1259                If an `Expression` instance is passed, it will be used as-is.
1260            distinct: set the DISTINCT flag if and only if this is true.
1261            dialect: the dialect used to parse the input expression.
1262            opts: other options to use to parse the input expressions.
1263
1264        Returns:
1265            The new Intersect expression.
1266        """
1267        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1268
1269    def except_(
1270        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1271    ) -> Except:
1272        """
1273        Builds an EXCEPT expression.
1274
1275        Example:
1276            >>> import sqlglot
1277            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1278            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1279
1280        Args:
1281            expression: the SQL code string.
1282                If an `Expression` instance is passed, it will be used as-is.
1283            distinct: set the DISTINCT flag if and only if this is true.
1284            dialect: the dialect used to parse the input expression.
1285            opts: other options to use to parse the input expressions.
1286
1287        Returns:
1288            The new Except expression.
1289        """
1290        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1291
1292
1293class UDTF(DerivedTable):
1294    @property
1295    def selects(self) -> t.List[Expression]:
1296        alias = self.args.get("alias")
1297        return alias.columns if alias else []
1298
1299
1300class Cache(Expression):
1301    arg_types = {
1302        "this": True,
1303        "lazy": False,
1304        "options": False,
1305        "expression": False,
1306    }
1307
1308
1309class Uncache(Expression):
1310    arg_types = {"this": True, "exists": False}
1311
1312
1313class Refresh(Expression):
1314    pass
1315
1316
1317class DDL(Expression):
1318    @property
1319    def ctes(self) -> t.List[CTE]:
1320        """Returns a list of all the CTEs attached to this statement."""
1321        with_ = self.args.get("with")
1322        return with_.expressions if with_ else []
1323
1324    @property
1325    def selects(self) -> t.List[Expression]:
1326        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1327        return self.expression.selects if isinstance(self.expression, Query) else []
1328
1329    @property
1330    def named_selects(self) -> t.List[str]:
1331        """
1332        If this statement contains a query (e.g. a CTAS), this returns the output
1333        names of the query's projections.
1334        """
1335        return self.expression.named_selects if isinstance(self.expression, Query) else []
1336
1337
1338class DML(Expression):
1339    def returning(
1340        self,
1341        expression: ExpOrStr,
1342        dialect: DialectType = None,
1343        copy: bool = True,
1344        **opts,
1345    ) -> DML:
1346        """
1347        Set the RETURNING expression. Not supported by all dialects.
1348
1349        Example:
1350            >>> delete("tbl").returning("*", dialect="postgres").sql()
1351            'DELETE FROM tbl RETURNING *'
1352
1353        Args:
1354            expression: the SQL code strings to parse.
1355                If an `Expression` instance is passed, it will be used as-is.
1356            dialect: the dialect used to parse the input expressions.
1357            copy: if `False`, modify this expression instance in-place.
1358            opts: other options to use to parse the input expressions.
1359
1360        Returns:
1361            Delete: the modified expression.
1362        """
1363        return _apply_builder(
1364            expression=expression,
1365            instance=self,
1366            arg="returning",
1367            prefix="RETURNING",
1368            dialect=dialect,
1369            copy=copy,
1370            into=Returning,
1371            **opts,
1372        )
1373
1374
1375class Create(DDL):
1376    arg_types = {
1377        "with": False,
1378        "this": True,
1379        "kind": True,
1380        "expression": False,
1381        "exists": False,
1382        "properties": False,
1383        "replace": False,
1384        "unique": False,
1385        "indexes": False,
1386        "no_schema_binding": False,
1387        "begin": False,
1388        "end": False,
1389        "clone": False,
1390    }
1391
1392    @property
1393    def kind(self) -> t.Optional[str]:
1394        kind = self.args.get("kind")
1395        return kind and kind.upper()
1396
1397
1398class SequenceProperties(Expression):
1399    arg_types = {
1400        "increment": False,
1401        "minvalue": False,
1402        "maxvalue": False,
1403        "cache": False,
1404        "start": False,
1405        "owned": False,
1406        "options": False,
1407    }
1408
1409
1410class TruncateTable(Expression):
1411    arg_types = {
1412        "expressions": True,
1413        "is_database": False,
1414        "exists": False,
1415        "only": False,
1416        "cluster": False,
1417        "identity": False,
1418        "option": False,
1419        "partition": False,
1420    }
1421
1422
1423# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1424# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1425# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1426class Clone(Expression):
1427    arg_types = {"this": True, "shallow": False, "copy": False}
1428
1429
1430class Describe(Expression):
1431    arg_types = {"this": True, "style": False, "kind": False, "expressions": False}
1432
1433
1434class Kill(Expression):
1435    arg_types = {"this": True, "kind": False}
1436
1437
1438class Pragma(Expression):
1439    pass
1440
1441
1442class Set(Expression):
1443    arg_types = {"expressions": False, "unset": False, "tag": False}
1444
1445
1446class Heredoc(Expression):
1447    arg_types = {"this": True, "tag": False}
1448
1449
1450class SetItem(Expression):
1451    arg_types = {
1452        "this": False,
1453        "expressions": False,
1454        "kind": False,
1455        "collate": False,  # MySQL SET NAMES statement
1456        "global": False,
1457    }
1458
1459
1460class Show(Expression):
1461    arg_types = {
1462        "this": True,
1463        "history": False,
1464        "terse": False,
1465        "target": False,
1466        "offset": False,
1467        "starts_with": False,
1468        "limit": False,
1469        "from": False,
1470        "like": False,
1471        "where": False,
1472        "db": False,
1473        "scope": False,
1474        "scope_kind": False,
1475        "full": False,
1476        "mutex": False,
1477        "query": False,
1478        "channel": False,
1479        "global": False,
1480        "log": False,
1481        "position": False,
1482        "types": False,
1483    }
1484
1485
1486class UserDefinedFunction(Expression):
1487    arg_types = {"this": True, "expressions": False, "wrapped": False}
1488
1489
1490class CharacterSet(Expression):
1491    arg_types = {"this": True, "default": False}
1492
1493
1494class With(Expression):
1495    arg_types = {"expressions": True, "recursive": False}
1496
1497    @property
1498    def recursive(self) -> bool:
1499        return bool(self.args.get("recursive"))
1500
1501
1502class WithinGroup(Expression):
1503    arg_types = {"this": True, "expression": False}
1504
1505
1506# clickhouse supports scalar ctes
1507# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1508class CTE(DerivedTable):
1509    arg_types = {
1510        "this": True,
1511        "alias": True,
1512        "scalar": False,
1513        "materialized": False,
1514    }
1515
1516
1517class TableAlias(Expression):
1518    arg_types = {"this": False, "columns": False}
1519
1520    @property
1521    def columns(self):
1522        return self.args.get("columns") or []
1523
1524
1525class BitString(Condition):
1526    pass
1527
1528
1529class HexString(Condition):
1530    pass
1531
1532
1533class ByteString(Condition):
1534    pass
1535
1536
1537class RawString(Condition):
1538    pass
1539
1540
1541class UnicodeString(Condition):
1542    arg_types = {"this": True, "escape": False}
1543
1544
1545class Column(Condition):
1546    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1547
1548    @property
1549    def table(self) -> str:
1550        return self.text("table")
1551
1552    @property
1553    def db(self) -> str:
1554        return self.text("db")
1555
1556    @property
1557    def catalog(self) -> str:
1558        return self.text("catalog")
1559
1560    @property
1561    def output_name(self) -> str:
1562        return self.name
1563
1564    @property
1565    def parts(self) -> t.List[Identifier]:
1566        """Return the parts of a column in order catalog, db, table, name."""
1567        return [
1568            t.cast(Identifier, self.args[part])
1569            for part in ("catalog", "db", "table", "this")
1570            if self.args.get(part)
1571        ]
1572
1573    def to_dot(self) -> Dot | Identifier:
1574        """Converts the column into a dot expression."""
1575        parts = self.parts
1576        parent = self.parent
1577
1578        while parent:
1579            if isinstance(parent, Dot):
1580                parts.append(parent.expression)
1581            parent = parent.parent
1582
1583        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1584
1585
1586class ColumnPosition(Expression):
1587    arg_types = {"this": False, "position": True}
1588
1589
1590class ColumnDef(Expression):
1591    arg_types = {
1592        "this": True,
1593        "kind": False,
1594        "constraints": False,
1595        "exists": False,
1596        "position": False,
1597    }
1598
1599    @property
1600    def constraints(self) -> t.List[ColumnConstraint]:
1601        return self.args.get("constraints") or []
1602
1603    @property
1604    def kind(self) -> t.Optional[DataType]:
1605        return self.args.get("kind")
1606
1607
1608class AlterColumn(Expression):
1609    arg_types = {
1610        "this": True,
1611        "dtype": False,
1612        "collate": False,
1613        "using": False,
1614        "default": False,
1615        "drop": False,
1616        "comment": False,
1617    }
1618
1619
1620class RenameColumn(Expression):
1621    arg_types = {"this": True, "to": True, "exists": False}
1622
1623
1624class RenameTable(Expression):
1625    pass
1626
1627
1628class SwapTable(Expression):
1629    pass
1630
1631
1632class Comment(Expression):
1633    arg_types = {
1634        "this": True,
1635        "kind": True,
1636        "expression": True,
1637        "exists": False,
1638        "materialized": False,
1639    }
1640
1641
1642class Comprehension(Expression):
1643    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1644
1645
1646# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1647class MergeTreeTTLAction(Expression):
1648    arg_types = {
1649        "this": True,
1650        "delete": False,
1651        "recompress": False,
1652        "to_disk": False,
1653        "to_volume": False,
1654    }
1655
1656
1657# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1658class MergeTreeTTL(Expression):
1659    arg_types = {
1660        "expressions": True,
1661        "where": False,
1662        "group": False,
1663        "aggregates": False,
1664    }
1665
1666
1667# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1668class IndexConstraintOption(Expression):
1669    arg_types = {
1670        "key_block_size": False,
1671        "using": False,
1672        "parser": False,
1673        "comment": False,
1674        "visible": False,
1675        "engine_attr": False,
1676        "secondary_engine_attr": False,
1677    }
1678
1679
1680class ColumnConstraint(Expression):
1681    arg_types = {"this": False, "kind": True}
1682
1683    @property
1684    def kind(self) -> ColumnConstraintKind:
1685        return self.args["kind"]
1686
1687
1688class ColumnConstraintKind(Expression):
1689    pass
1690
1691
1692class AutoIncrementColumnConstraint(ColumnConstraintKind):
1693    pass
1694
1695
1696class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1697    arg_types = {"this": True, "expression": True}
1698
1699
1700class CaseSpecificColumnConstraint(ColumnConstraintKind):
1701    arg_types = {"not_": True}
1702
1703
1704class CharacterSetColumnConstraint(ColumnConstraintKind):
1705    arg_types = {"this": True}
1706
1707
1708class CheckColumnConstraint(ColumnConstraintKind):
1709    arg_types = {"this": True, "enforced": False}
1710
1711
1712class ClusteredColumnConstraint(ColumnConstraintKind):
1713    pass
1714
1715
1716class CollateColumnConstraint(ColumnConstraintKind):
1717    pass
1718
1719
1720class CommentColumnConstraint(ColumnConstraintKind):
1721    pass
1722
1723
1724class CompressColumnConstraint(ColumnConstraintKind):
1725    pass
1726
1727
1728class DateFormatColumnConstraint(ColumnConstraintKind):
1729    arg_types = {"this": True}
1730
1731
1732class DefaultColumnConstraint(ColumnConstraintKind):
1733    pass
1734
1735
1736class EncodeColumnConstraint(ColumnConstraintKind):
1737    pass
1738
1739
1740# https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
1741class ExcludeColumnConstraint(ColumnConstraintKind):
1742    pass
1743
1744
1745class EphemeralColumnConstraint(ColumnConstraintKind):
1746    arg_types = {"this": False}
1747
1748
1749class WithOperator(Expression):
1750    arg_types = {"this": True, "op": True}
1751
1752
1753class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1754    # this: True -> ALWAYS, this: False -> BY DEFAULT
1755    arg_types = {
1756        "this": False,
1757        "expression": False,
1758        "on_null": False,
1759        "start": False,
1760        "increment": False,
1761        "minvalue": False,
1762        "maxvalue": False,
1763        "cycle": False,
1764    }
1765
1766
1767class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1768    arg_types = {"start": False, "hidden": False}
1769
1770
1771# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1772# https://github.com/ClickHouse/ClickHouse/blob/master/src/Parsers/ParserCreateQuery.h#L646
1773class IndexColumnConstraint(ColumnConstraintKind):
1774    arg_types = {
1775        "this": False,
1776        "expressions": False,
1777        "kind": False,
1778        "index_type": False,
1779        "options": False,
1780        "expression": False,  # Clickhouse
1781        "granularity": False,
1782    }
1783
1784
1785class InlineLengthColumnConstraint(ColumnConstraintKind):
1786    pass
1787
1788
1789class NonClusteredColumnConstraint(ColumnConstraintKind):
1790    pass
1791
1792
1793class NotForReplicationColumnConstraint(ColumnConstraintKind):
1794    arg_types = {}
1795
1796
1797class NotNullColumnConstraint(ColumnConstraintKind):
1798    arg_types = {"allow_null": False}
1799
1800
1801# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1802class OnUpdateColumnConstraint(ColumnConstraintKind):
1803    pass
1804
1805
1806# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1807class TransformColumnConstraint(ColumnConstraintKind):
1808    pass
1809
1810
1811class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1812    arg_types = {"desc": False}
1813
1814
1815class TitleColumnConstraint(ColumnConstraintKind):
1816    pass
1817
1818
1819class UniqueColumnConstraint(ColumnConstraintKind):
1820    arg_types = {"this": False, "index_type": False, "on_conflict": False}
1821
1822
1823class UppercaseColumnConstraint(ColumnConstraintKind):
1824    arg_types: t.Dict[str, t.Any] = {}
1825
1826
1827class PathColumnConstraint(ColumnConstraintKind):
1828    pass
1829
1830
1831# computed column expression
1832# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1833class ComputedColumnConstraint(ColumnConstraintKind):
1834    arg_types = {"this": True, "persisted": False, "not_null": False}
1835
1836
1837class Constraint(Expression):
1838    arg_types = {"this": True, "expressions": True}
1839
1840
1841class Delete(DML):
1842    arg_types = {
1843        "with": False,
1844        "this": False,
1845        "using": False,
1846        "where": False,
1847        "returning": False,
1848        "limit": False,
1849        "tables": False,  # Multiple-Table Syntax (MySQL)
1850    }
1851
1852    def delete(
1853        self,
1854        table: ExpOrStr,
1855        dialect: DialectType = None,
1856        copy: bool = True,
1857        **opts,
1858    ) -> Delete:
1859        """
1860        Create a DELETE expression or replace the table on an existing DELETE expression.
1861
1862        Example:
1863            >>> delete("tbl").sql()
1864            'DELETE FROM tbl'
1865
1866        Args:
1867            table: the table from which to delete.
1868            dialect: the dialect used to parse the input expression.
1869            copy: if `False`, modify this expression instance in-place.
1870            opts: other options to use to parse the input expressions.
1871
1872        Returns:
1873            Delete: the modified expression.
1874        """
1875        return _apply_builder(
1876            expression=table,
1877            instance=self,
1878            arg="this",
1879            dialect=dialect,
1880            into=Table,
1881            copy=copy,
1882            **opts,
1883        )
1884
1885    def where(
1886        self,
1887        *expressions: t.Optional[ExpOrStr],
1888        append: bool = True,
1889        dialect: DialectType = None,
1890        copy: bool = True,
1891        **opts,
1892    ) -> Delete:
1893        """
1894        Append to or set the WHERE expressions.
1895
1896        Example:
1897            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1898            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1899
1900        Args:
1901            *expressions: the SQL code strings to parse.
1902                If an `Expression` instance is passed, it will be used as-is.
1903                Multiple expressions are combined with an AND operator.
1904            append: if `True`, AND the new expressions to any existing expression.
1905                Otherwise, this resets the expression.
1906            dialect: the dialect used to parse the input expressions.
1907            copy: if `False`, modify this expression instance in-place.
1908            opts: other options to use to parse the input expressions.
1909
1910        Returns:
1911            Delete: the modified expression.
1912        """
1913        return _apply_conjunction_builder(
1914            *expressions,
1915            instance=self,
1916            arg="where",
1917            append=append,
1918            into=Where,
1919            dialect=dialect,
1920            copy=copy,
1921            **opts,
1922        )
1923
1924
1925class Drop(Expression):
1926    arg_types = {
1927        "this": False,
1928        "kind": False,
1929        "expressions": False,
1930        "exists": False,
1931        "temporary": False,
1932        "materialized": False,
1933        "cascade": False,
1934        "constraints": False,
1935        "purge": False,
1936    }
1937
1938
1939class Filter(Expression):
1940    arg_types = {"this": True, "expression": True}
1941
1942
1943class Check(Expression):
1944    pass
1945
1946
1947# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1948class Connect(Expression):
1949    arg_types = {"start": False, "connect": True, "nocycle": False}
1950
1951
1952class Prior(Expression):
1953    pass
1954
1955
1956class Directory(Expression):
1957    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1958    arg_types = {"this": True, "local": False, "row_format": False}
1959
1960
1961class ForeignKey(Expression):
1962    arg_types = {
1963        "expressions": True,
1964        "reference": False,
1965        "delete": False,
1966        "update": False,
1967    }
1968
1969
1970class ColumnPrefix(Expression):
1971    arg_types = {"this": True, "expression": True}
1972
1973
1974class PrimaryKey(Expression):
1975    arg_types = {"expressions": True, "options": False}
1976
1977
1978# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1979# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1980class Into(Expression):
1981    arg_types = {"this": True, "temporary": False, "unlogged": False}
1982
1983
1984class From(Expression):
1985    @property
1986    def name(self) -> str:
1987        return self.this.name
1988
1989    @property
1990    def alias_or_name(self) -> str:
1991        return self.this.alias_or_name
1992
1993
1994class Having(Expression):
1995    pass
1996
1997
1998class Hint(Expression):
1999    arg_types = {"expressions": True}
2000
2001
2002class JoinHint(Expression):
2003    arg_types = {"this": True, "expressions": True}
2004
2005
2006class Identifier(Expression):
2007    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
2008
2009    @property
2010    def quoted(self) -> bool:
2011        return bool(self.args.get("quoted"))
2012
2013    @property
2014    def hashable_args(self) -> t.Any:
2015        return (self.this, self.quoted)
2016
2017    @property
2018    def output_name(self) -> str:
2019        return self.name
2020
2021
2022# https://www.postgresql.org/docs/current/indexes-opclass.html
2023class Opclass(Expression):
2024    arg_types = {"this": True, "expression": True}
2025
2026
2027class Index(Expression):
2028    arg_types = {
2029        "this": False,
2030        "table": False,
2031        "unique": False,
2032        "primary": False,
2033        "amp": False,  # teradata
2034        "params": False,
2035    }
2036
2037
2038class IndexParameters(Expression):
2039    arg_types = {
2040        "using": False,
2041        "include": False,
2042        "columns": False,
2043        "with_storage": False,
2044        "partition_by": False,
2045        "tablespace": False,
2046        "where": False,
2047    }
2048
2049
2050class Insert(DDL, DML):
2051    arg_types = {
2052        "hint": False,
2053        "with": False,
2054        "is_function": False,
2055        "this": True,
2056        "expression": False,
2057        "conflict": False,
2058        "returning": False,
2059        "overwrite": False,
2060        "exists": False,
2061        "partition": False,
2062        "alternative": False,
2063        "where": False,
2064        "ignore": False,
2065        "by_name": False,
2066    }
2067
2068    def with_(
2069        self,
2070        alias: ExpOrStr,
2071        as_: ExpOrStr,
2072        recursive: t.Optional[bool] = None,
2073        append: bool = True,
2074        dialect: DialectType = None,
2075        copy: bool = True,
2076        **opts,
2077    ) -> Insert:
2078        """
2079        Append to or set the common table expressions.
2080
2081        Example:
2082            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
2083            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
2084
2085        Args:
2086            alias: the SQL code string to parse as the table name.
2087                If an `Expression` instance is passed, this is used as-is.
2088            as_: the SQL code string to parse as the table expression.
2089                If an `Expression` instance is passed, it will be used as-is.
2090            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2091            append: if `True`, add to any existing expressions.
2092                Otherwise, this resets the expressions.
2093            dialect: the dialect used to parse the input expression.
2094            copy: if `False`, modify this expression instance in-place.
2095            opts: other options to use to parse the input expressions.
2096
2097        Returns:
2098            The modified expression.
2099        """
2100        return _apply_cte_builder(
2101            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2102        )
2103
2104
2105class OnConflict(Expression):
2106    arg_types = {
2107        "duplicate": False,
2108        "expressions": False,
2109        "action": False,
2110        "conflict_keys": False,
2111        "constraint": False,
2112    }
2113
2114
2115class Returning(Expression):
2116    arg_types = {"expressions": True, "into": False}
2117
2118
2119# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
2120class Introducer(Expression):
2121    arg_types = {"this": True, "expression": True}
2122
2123
2124# national char, like n'utf8'
2125class National(Expression):
2126    pass
2127
2128
2129class LoadData(Expression):
2130    arg_types = {
2131        "this": True,
2132        "local": False,
2133        "overwrite": False,
2134        "inpath": True,
2135        "partition": False,
2136        "input_format": False,
2137        "serde": False,
2138    }
2139
2140
2141class Partition(Expression):
2142    arg_types = {"expressions": True}
2143
2144
2145class PartitionRange(Expression):
2146    arg_types = {"this": True, "expression": True}
2147
2148
2149class Fetch(Expression):
2150    arg_types = {
2151        "direction": False,
2152        "count": False,
2153        "percent": False,
2154        "with_ties": False,
2155    }
2156
2157
2158class Group(Expression):
2159    arg_types = {
2160        "expressions": False,
2161        "grouping_sets": False,
2162        "cube": False,
2163        "rollup": False,
2164        "totals": False,
2165        "all": False,
2166    }
2167
2168
2169class Lambda(Expression):
2170    arg_types = {"this": True, "expressions": True}
2171
2172
2173class Limit(Expression):
2174    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
2175
2176
2177class Literal(Condition):
2178    arg_types = {"this": True, "is_string": True}
2179
2180    @property
2181    def hashable_args(self) -> t.Any:
2182        return (self.this, self.args.get("is_string"))
2183
2184    @classmethod
2185    def number(cls, number) -> Literal:
2186        return cls(this=str(number), is_string=False)
2187
2188    @classmethod
2189    def string(cls, string) -> Literal:
2190        return cls(this=str(string), is_string=True)
2191
2192    @property
2193    def output_name(self) -> str:
2194        return self.name
2195
2196
2197class Join(Expression):
2198    arg_types = {
2199        "this": True,
2200        "on": False,
2201        "side": False,
2202        "kind": False,
2203        "using": False,
2204        "method": False,
2205        "global": False,
2206        "hint": False,
2207        "match_condition": False,  # Snowflake
2208    }
2209
2210    @property
2211    def method(self) -> str:
2212        return self.text("method").upper()
2213
2214    @property
2215    def kind(self) -> str:
2216        return self.text("kind").upper()
2217
2218    @property
2219    def side(self) -> str:
2220        return self.text("side").upper()
2221
2222    @property
2223    def hint(self) -> str:
2224        return self.text("hint").upper()
2225
2226    @property
2227    def alias_or_name(self) -> str:
2228        return self.this.alias_or_name
2229
2230    def on(
2231        self,
2232        *expressions: t.Optional[ExpOrStr],
2233        append: bool = True,
2234        dialect: DialectType = None,
2235        copy: bool = True,
2236        **opts,
2237    ) -> Join:
2238        """
2239        Append to or set the ON expressions.
2240
2241        Example:
2242            >>> import sqlglot
2243            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2244            'JOIN x ON y = 1'
2245
2246        Args:
2247            *expressions: the SQL code strings to parse.
2248                If an `Expression` instance is passed, it will be used as-is.
2249                Multiple expressions are combined with an AND operator.
2250            append: if `True`, AND the new expressions to any existing expression.
2251                Otherwise, this resets the expression.
2252            dialect: the dialect used to parse the input expressions.
2253            copy: if `False`, modify this expression instance in-place.
2254            opts: other options to use to parse the input expressions.
2255
2256        Returns:
2257            The modified Join expression.
2258        """
2259        join = _apply_conjunction_builder(
2260            *expressions,
2261            instance=self,
2262            arg="on",
2263            append=append,
2264            dialect=dialect,
2265            copy=copy,
2266            **opts,
2267        )
2268
2269        if join.kind == "CROSS":
2270            join.set("kind", None)
2271
2272        return join
2273
2274    def using(
2275        self,
2276        *expressions: t.Optional[ExpOrStr],
2277        append: bool = True,
2278        dialect: DialectType = None,
2279        copy: bool = True,
2280        **opts,
2281    ) -> Join:
2282        """
2283        Append to or set the USING expressions.
2284
2285        Example:
2286            >>> import sqlglot
2287            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2288            'JOIN x USING (foo, bla)'
2289
2290        Args:
2291            *expressions: the SQL code strings to parse.
2292                If an `Expression` instance is passed, it will be used as-is.
2293            append: if `True`, concatenate the new expressions to the existing "using" list.
2294                Otherwise, this resets the expression.
2295            dialect: the dialect used to parse the input expressions.
2296            copy: if `False`, modify this expression instance in-place.
2297            opts: other options to use to parse the input expressions.
2298
2299        Returns:
2300            The modified Join expression.
2301        """
2302        join = _apply_list_builder(
2303            *expressions,
2304            instance=self,
2305            arg="using",
2306            append=append,
2307            dialect=dialect,
2308            copy=copy,
2309            **opts,
2310        )
2311
2312        if join.kind == "CROSS":
2313            join.set("kind", None)
2314
2315        return join
2316
2317
2318class Lateral(UDTF):
2319    arg_types = {
2320        "this": True,
2321        "view": False,
2322        "outer": False,
2323        "alias": False,
2324        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2325    }
2326
2327
2328class MatchRecognizeMeasure(Expression):
2329    arg_types = {
2330        "this": True,
2331        "window_frame": False,
2332    }
2333
2334
2335class MatchRecognize(Expression):
2336    arg_types = {
2337        "partition_by": False,
2338        "order": False,
2339        "measures": False,
2340        "rows": False,
2341        "after": False,
2342        "pattern": False,
2343        "define": False,
2344        "alias": False,
2345    }
2346
2347
2348# Clickhouse FROM FINAL modifier
2349# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2350class Final(Expression):
2351    pass
2352
2353
2354class Offset(Expression):
2355    arg_types = {"this": False, "expression": True, "expressions": False}
2356
2357
2358class Order(Expression):
2359    arg_types = {
2360        "this": False,
2361        "expressions": True,
2362        "interpolate": False,
2363        "siblings": False,
2364    }
2365
2366
2367# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2368class WithFill(Expression):
2369    arg_types = {"from": False, "to": False, "step": False}
2370
2371
2372# hive specific sorts
2373# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2374class Cluster(Order):
2375    pass
2376
2377
2378class Distribute(Order):
2379    pass
2380
2381
2382class Sort(Order):
2383    pass
2384
2385
2386class Ordered(Expression):
2387    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2388
2389
2390class Property(Expression):
2391    arg_types = {"this": True, "value": True}
2392
2393
2394class AlgorithmProperty(Property):
2395    arg_types = {"this": True}
2396
2397
2398class AutoIncrementProperty(Property):
2399    arg_types = {"this": True}
2400
2401
2402# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2403class AutoRefreshProperty(Property):
2404    arg_types = {"this": True}
2405
2406
2407class BackupProperty(Property):
2408    arg_types = {"this": True}
2409
2410
2411class BlockCompressionProperty(Property):
2412    arg_types = {
2413        "autotemp": False,
2414        "always": False,
2415        "default": False,
2416        "manual": False,
2417        "never": False,
2418    }
2419
2420
2421class CharacterSetProperty(Property):
2422    arg_types = {"this": True, "default": True}
2423
2424
2425class ChecksumProperty(Property):
2426    arg_types = {"on": False, "default": False}
2427
2428
2429class CollateProperty(Property):
2430    arg_types = {"this": True, "default": False}
2431
2432
2433class CopyGrantsProperty(Property):
2434    arg_types = {}
2435
2436
2437class DataBlocksizeProperty(Property):
2438    arg_types = {
2439        "size": False,
2440        "units": False,
2441        "minimum": False,
2442        "maximum": False,
2443        "default": False,
2444    }
2445
2446
2447class DefinerProperty(Property):
2448    arg_types = {"this": True}
2449
2450
2451class DistKeyProperty(Property):
2452    arg_types = {"this": True}
2453
2454
2455class DistStyleProperty(Property):
2456    arg_types = {"this": True}
2457
2458
2459class EngineProperty(Property):
2460    arg_types = {"this": True}
2461
2462
2463class HeapProperty(Property):
2464    arg_types = {}
2465
2466
2467class ToTableProperty(Property):
2468    arg_types = {"this": True}
2469
2470
2471class ExecuteAsProperty(Property):
2472    arg_types = {"this": True}
2473
2474
2475class ExternalProperty(Property):
2476    arg_types = {"this": False}
2477
2478
2479class FallbackProperty(Property):
2480    arg_types = {"no": True, "protection": False}
2481
2482
2483class FileFormatProperty(Property):
2484    arg_types = {"this": True}
2485
2486
2487class FreespaceProperty(Property):
2488    arg_types = {"this": True, "percent": False}
2489
2490
2491class GlobalProperty(Property):
2492    arg_types = {}
2493
2494
2495class IcebergProperty(Property):
2496    arg_types = {}
2497
2498
2499class InheritsProperty(Property):
2500    arg_types = {"expressions": True}
2501
2502
2503class InputModelProperty(Property):
2504    arg_types = {"this": True}
2505
2506
2507class OutputModelProperty(Property):
2508    arg_types = {"this": True}
2509
2510
2511class IsolatedLoadingProperty(Property):
2512    arg_types = {"no": False, "concurrent": False, "target": False}
2513
2514
2515class JournalProperty(Property):
2516    arg_types = {
2517        "no": False,
2518        "dual": False,
2519        "before": False,
2520        "local": False,
2521        "after": False,
2522    }
2523
2524
2525class LanguageProperty(Property):
2526    arg_types = {"this": True}
2527
2528
2529# spark ddl
2530class ClusteredByProperty(Property):
2531    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2532
2533
2534class DictProperty(Property):
2535    arg_types = {"this": True, "kind": True, "settings": False}
2536
2537
2538class DictSubProperty(Property):
2539    pass
2540
2541
2542class DictRange(Property):
2543    arg_types = {"this": True, "min": True, "max": True}
2544
2545
2546# Clickhouse CREATE ... ON CLUSTER modifier
2547# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2548class OnCluster(Property):
2549    arg_types = {"this": True}
2550
2551
2552class LikeProperty(Property):
2553    arg_types = {"this": True, "expressions": False}
2554
2555
2556class LocationProperty(Property):
2557    arg_types = {"this": True}
2558
2559
2560class LockProperty(Property):
2561    arg_types = {"this": True}
2562
2563
2564class LockingProperty(Property):
2565    arg_types = {
2566        "this": False,
2567        "kind": True,
2568        "for_or_in": False,
2569        "lock_type": True,
2570        "override": False,
2571    }
2572
2573
2574class LogProperty(Property):
2575    arg_types = {"no": True}
2576
2577
2578class MaterializedProperty(Property):
2579    arg_types = {"this": False}
2580
2581
2582class MergeBlockRatioProperty(Property):
2583    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2584
2585
2586class NoPrimaryIndexProperty(Property):
2587    arg_types = {}
2588
2589
2590class OnProperty(Property):
2591    arg_types = {"this": True}
2592
2593
2594class OnCommitProperty(Property):
2595    arg_types = {"delete": False}
2596
2597
2598class PartitionedByProperty(Property):
2599    arg_types = {"this": True}
2600
2601
2602# https://www.postgresql.org/docs/current/sql-createtable.html
2603class PartitionBoundSpec(Expression):
2604    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2605    arg_types = {
2606        "this": False,
2607        "expression": False,
2608        "from_expressions": False,
2609        "to_expressions": False,
2610    }
2611
2612
2613class PartitionedOfProperty(Property):
2614    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2615    arg_types = {"this": True, "expression": True}
2616
2617
2618class RemoteWithConnectionModelProperty(Property):
2619    arg_types = {"this": True}
2620
2621
2622class ReturnsProperty(Property):
2623    arg_types = {"this": True, "is_table": False, "table": False}
2624
2625
2626class RowFormatProperty(Property):
2627    arg_types = {"this": True}
2628
2629
2630class RowFormatDelimitedProperty(Property):
2631    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2632    arg_types = {
2633        "fields": False,
2634        "escaped": False,
2635        "collection_items": False,
2636        "map_keys": False,
2637        "lines": False,
2638        "null": False,
2639        "serde": False,
2640    }
2641
2642
2643class RowFormatSerdeProperty(Property):
2644    arg_types = {"this": True, "serde_properties": False}
2645
2646
2647# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2648class QueryTransform(Expression):
2649    arg_types = {
2650        "expressions": True,
2651        "command_script": True,
2652        "schema": False,
2653        "row_format_before": False,
2654        "record_writer": False,
2655        "row_format_after": False,
2656        "record_reader": False,
2657    }
2658
2659
2660class SampleProperty(Property):
2661    arg_types = {"this": True}
2662
2663
2664class SchemaCommentProperty(Property):
2665    arg_types = {"this": True}
2666
2667
2668class SerdeProperties(Property):
2669    arg_types = {"expressions": True}
2670
2671
2672class SetProperty(Property):
2673    arg_types = {"multi": True}
2674
2675
2676class SharingProperty(Property):
2677    arg_types = {"this": False}
2678
2679
2680class SetConfigProperty(Property):
2681    arg_types = {"this": True}
2682
2683
2684class SettingsProperty(Property):
2685    arg_types = {"expressions": True}
2686
2687
2688class SortKeyProperty(Property):
2689    arg_types = {"this": True, "compound": False}
2690
2691
2692class SqlReadWriteProperty(Property):
2693    arg_types = {"this": True}
2694
2695
2696class SqlSecurityProperty(Property):
2697    arg_types = {"definer": True}
2698
2699
2700class StabilityProperty(Property):
2701    arg_types = {"this": True}
2702
2703
2704class TemporaryProperty(Property):
2705    arg_types = {"this": False}
2706
2707
2708class TransformModelProperty(Property):
2709    arg_types = {"expressions": True}
2710
2711
2712class TransientProperty(Property):
2713    arg_types = {"this": False}
2714
2715
2716class UnloggedProperty(Property):
2717    arg_types = {}
2718
2719
2720# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver16
2721class ViewAttributeProperty(Property):
2722    arg_types = {"this": True}
2723
2724
2725class VolatileProperty(Property):
2726    arg_types = {"this": False}
2727
2728
2729class WithDataProperty(Property):
2730    arg_types = {"no": True, "statistics": False}
2731
2732
2733class WithJournalTableProperty(Property):
2734    arg_types = {"this": True}
2735
2736
2737class WithSystemVersioningProperty(Property):
2738    # this -> history table name, expression -> data consistency check
2739    arg_types = {"this": False, "expression": False}
2740
2741
2742class Properties(Expression):
2743    arg_types = {"expressions": True}
2744
2745    NAME_TO_PROPERTY = {
2746        "ALGORITHM": AlgorithmProperty,
2747        "AUTO_INCREMENT": AutoIncrementProperty,
2748        "CHARACTER SET": CharacterSetProperty,
2749        "CLUSTERED_BY": ClusteredByProperty,
2750        "COLLATE": CollateProperty,
2751        "COMMENT": SchemaCommentProperty,
2752        "DEFINER": DefinerProperty,
2753        "DISTKEY": DistKeyProperty,
2754        "DISTSTYLE": DistStyleProperty,
2755        "ENGINE": EngineProperty,
2756        "EXECUTE AS": ExecuteAsProperty,
2757        "FORMAT": FileFormatProperty,
2758        "LANGUAGE": LanguageProperty,
2759        "LOCATION": LocationProperty,
2760        "LOCK": LockProperty,
2761        "PARTITIONED_BY": PartitionedByProperty,
2762        "RETURNS": ReturnsProperty,
2763        "ROW_FORMAT": RowFormatProperty,
2764        "SORTKEY": SortKeyProperty,
2765    }
2766
2767    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2768
2769    # CREATE property locations
2770    # Form: schema specified
2771    #   create [POST_CREATE]
2772    #     table a [POST_NAME]
2773    #     (b int) [POST_SCHEMA]
2774    #     with ([POST_WITH])
2775    #     index (b) [POST_INDEX]
2776    #
2777    # Form: alias selection
2778    #   create [POST_CREATE]
2779    #     table a [POST_NAME]
2780    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2781    #     index (c) [POST_INDEX]
2782    class Location(AutoName):
2783        POST_CREATE = auto()
2784        POST_NAME = auto()
2785        POST_SCHEMA = auto()
2786        POST_WITH = auto()
2787        POST_ALIAS = auto()
2788        POST_EXPRESSION = auto()
2789        POST_INDEX = auto()
2790        UNSUPPORTED = auto()
2791
2792    @classmethod
2793    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2794        expressions = []
2795        for key, value in properties_dict.items():
2796            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2797            if property_cls:
2798                expressions.append(property_cls(this=convert(value)))
2799            else:
2800                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2801
2802        return cls(expressions=expressions)
2803
2804
2805class Qualify(Expression):
2806    pass
2807
2808
2809class InputOutputFormat(Expression):
2810    arg_types = {"input_format": False, "output_format": False}
2811
2812
2813# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2814class Return(Expression):
2815    pass
2816
2817
2818class Reference(Expression):
2819    arg_types = {"this": True, "expressions": False, "options": False}
2820
2821
2822class Tuple(Expression):
2823    arg_types = {"expressions": False}
2824
2825    def isin(
2826        self,
2827        *expressions: t.Any,
2828        query: t.Optional[ExpOrStr] = None,
2829        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2830        copy: bool = True,
2831        **opts,
2832    ) -> In:
2833        return In(
2834            this=maybe_copy(self, copy),
2835            expressions=[convert(e, copy=copy) for e in expressions],
2836            query=maybe_parse(query, copy=copy, **opts) if query else None,
2837            unnest=(
2838                Unnest(
2839                    expressions=[
2840                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
2841                        for e in ensure_list(unnest)
2842                    ]
2843                )
2844                if unnest
2845                else None
2846            ),
2847        )
2848
2849
2850QUERY_MODIFIERS = {
2851    "match": False,
2852    "laterals": False,
2853    "joins": False,
2854    "connect": False,
2855    "pivots": False,
2856    "prewhere": False,
2857    "where": False,
2858    "group": False,
2859    "having": False,
2860    "qualify": False,
2861    "windows": False,
2862    "distribute": False,
2863    "sort": False,
2864    "cluster": False,
2865    "order": False,
2866    "limit": False,
2867    "offset": False,
2868    "locks": False,
2869    "sample": False,
2870    "settings": False,
2871    "format": False,
2872    "options": False,
2873}
2874
2875
2876# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16
2877# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16
2878class QueryOption(Expression):
2879    arg_types = {"this": True, "expression": False}
2880
2881
2882# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2883class WithTableHint(Expression):
2884    arg_types = {"expressions": True}
2885
2886
2887# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2888class IndexTableHint(Expression):
2889    arg_types = {"this": True, "expressions": False, "target": False}
2890
2891
2892# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2893class HistoricalData(Expression):
2894    arg_types = {"this": True, "kind": True, "expression": True}
2895
2896
2897class Table(Expression):
2898    arg_types = {
2899        "this": False,
2900        "alias": False,
2901        "db": False,
2902        "catalog": False,
2903        "laterals": False,
2904        "joins": False,
2905        "pivots": False,
2906        "hints": False,
2907        "system_time": False,
2908        "version": False,
2909        "format": False,
2910        "pattern": False,
2911        "ordinality": False,
2912        "when": False,
2913        "only": False,
2914    }
2915
2916    @property
2917    def name(self) -> str:
2918        if isinstance(self.this, Func):
2919            return ""
2920        return self.this.name
2921
2922    @property
2923    def db(self) -> str:
2924        return self.text("db")
2925
2926    @property
2927    def catalog(self) -> str:
2928        return self.text("catalog")
2929
2930    @property
2931    def selects(self) -> t.List[Expression]:
2932        return []
2933
2934    @property
2935    def named_selects(self) -> t.List[str]:
2936        return []
2937
2938    @property
2939    def parts(self) -> t.List[Expression]:
2940        """Return the parts of a table in order catalog, db, table."""
2941        parts: t.List[Expression] = []
2942
2943        for arg in ("catalog", "db", "this"):
2944            part = self.args.get(arg)
2945
2946            if isinstance(part, Dot):
2947                parts.extend(part.flatten())
2948            elif isinstance(part, Expression):
2949                parts.append(part)
2950
2951        return parts
2952
2953    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
2954        parts = self.parts
2955        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
2956        alias = self.args.get("alias")
2957        if alias:
2958            col = alias_(col, alias.this, copy=copy)
2959        return col
2960
2961
2962class Union(Query):
2963    arg_types = {
2964        "with": False,
2965        "this": True,
2966        "expression": True,
2967        "distinct": False,
2968        "by_name": False,
2969        **QUERY_MODIFIERS,
2970    }
2971
2972    def select(
2973        self,
2974        *expressions: t.Optional[ExpOrStr],
2975        append: bool = True,
2976        dialect: DialectType = None,
2977        copy: bool = True,
2978        **opts,
2979    ) -> Union:
2980        this = maybe_copy(self, copy)
2981        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2982        this.expression.unnest().select(
2983            *expressions, append=append, dialect=dialect, copy=False, **opts
2984        )
2985        return this
2986
2987    @property
2988    def named_selects(self) -> t.List[str]:
2989        return self.this.unnest().named_selects
2990
2991    @property
2992    def is_star(self) -> bool:
2993        return self.this.is_star or self.expression.is_star
2994
2995    @property
2996    def selects(self) -> t.List[Expression]:
2997        return self.this.unnest().selects
2998
2999    @property
3000    def left(self) -> Expression:
3001        return self.this
3002
3003    @property
3004    def right(self) -> Expression:
3005        return self.expression
3006
3007
3008class Except(Union):
3009    pass
3010
3011
3012class Intersect(Union):
3013    pass
3014
3015
3016class Unnest(UDTF):
3017    arg_types = {
3018        "expressions": True,
3019        "alias": False,
3020        "offset": False,
3021    }
3022
3023    @property
3024    def selects(self) -> t.List[Expression]:
3025        columns = super().selects
3026        offset = self.args.get("offset")
3027        if offset:
3028            columns = columns + [to_identifier("offset") if offset is True else offset]
3029        return columns
3030
3031
3032class Update(Expression):
3033    arg_types = {
3034        "with": False,
3035        "this": False,
3036        "expressions": True,
3037        "from": False,
3038        "where": False,
3039        "returning": False,
3040        "order": False,
3041        "limit": False,
3042    }
3043
3044
3045class Values(UDTF):
3046    arg_types = {"expressions": True, "alias": False}
3047
3048
3049class Var(Expression):
3050    pass
3051
3052
3053class Version(Expression):
3054    """
3055    Time travel, iceberg, bigquery etc
3056    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
3057    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
3058    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
3059    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
3060    this is either TIMESTAMP or VERSION
3061    kind is ("AS OF", "BETWEEN")
3062    """
3063
3064    arg_types = {"this": True, "kind": True, "expression": False}
3065
3066
3067class Schema(Expression):
3068    arg_types = {"this": False, "expressions": False}
3069
3070
3071# https://dev.mysql.com/doc/refman/8.0/en/select.html
3072# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
3073class Lock(Expression):
3074    arg_types = {"update": True, "expressions": False, "wait": False}
3075
3076
3077class Select(Query):
3078    arg_types = {
3079        "with": False,
3080        "kind": False,
3081        "expressions": False,
3082        "hint": False,
3083        "distinct": False,
3084        "into": False,
3085        "from": False,
3086        **QUERY_MODIFIERS,
3087    }
3088
3089    def from_(
3090        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
3091    ) -> Select:
3092        """
3093        Set the FROM expression.
3094
3095        Example:
3096            >>> Select().from_("tbl").select("x").sql()
3097            'SELECT x FROM tbl'
3098
3099        Args:
3100            expression : the SQL code strings to parse.
3101                If a `From` instance is passed, this is used as-is.
3102                If another `Expression` instance is passed, it will be wrapped in a `From`.
3103            dialect: the dialect used to parse the input expression.
3104            copy: if `False`, modify this expression instance in-place.
3105            opts: other options to use to parse the input expressions.
3106
3107        Returns:
3108            The modified Select expression.
3109        """
3110        return _apply_builder(
3111            expression=expression,
3112            instance=self,
3113            arg="from",
3114            into=From,
3115            prefix="FROM",
3116            dialect=dialect,
3117            copy=copy,
3118            **opts,
3119        )
3120
3121    def group_by(
3122        self,
3123        *expressions: t.Optional[ExpOrStr],
3124        append: bool = True,
3125        dialect: DialectType = None,
3126        copy: bool = True,
3127        **opts,
3128    ) -> Select:
3129        """
3130        Set the GROUP BY expression.
3131
3132        Example:
3133            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
3134            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
3135
3136        Args:
3137            *expressions: the SQL code strings to parse.
3138                If a `Group` instance is passed, this is used as-is.
3139                If another `Expression` instance is passed, it will be wrapped in a `Group`.
3140                If nothing is passed in then a group by is not applied to the expression
3141            append: if `True`, add to any existing expressions.
3142                Otherwise, this flattens all the `Group` expression into a single expression.
3143            dialect: the dialect used to parse the input expression.
3144            copy: if `False`, modify this expression instance in-place.
3145            opts: other options to use to parse the input expressions.
3146
3147        Returns:
3148            The modified Select expression.
3149        """
3150        if not expressions:
3151            return self if not copy else self.copy()
3152
3153        return _apply_child_list_builder(
3154            *expressions,
3155            instance=self,
3156            arg="group",
3157            append=append,
3158            copy=copy,
3159            prefix="GROUP BY",
3160            into=Group,
3161            dialect=dialect,
3162            **opts,
3163        )
3164
3165    def sort_by(
3166        self,
3167        *expressions: t.Optional[ExpOrStr],
3168        append: bool = True,
3169        dialect: DialectType = None,
3170        copy: bool = True,
3171        **opts,
3172    ) -> Select:
3173        """
3174        Set the SORT BY expression.
3175
3176        Example:
3177            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
3178            'SELECT x FROM tbl SORT BY x DESC'
3179
3180        Args:
3181            *expressions: the SQL code strings to parse.
3182                If a `Group` instance is passed, this is used as-is.
3183                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
3184            append: if `True`, add to any existing expressions.
3185                Otherwise, this flattens all the `Order` expression into a single expression.
3186            dialect: the dialect used to parse the input expression.
3187            copy: if `False`, modify this expression instance in-place.
3188            opts: other options to use to parse the input expressions.
3189
3190        Returns:
3191            The modified Select expression.
3192        """
3193        return _apply_child_list_builder(
3194            *expressions,
3195            instance=self,
3196            arg="sort",
3197            append=append,
3198            copy=copy,
3199            prefix="SORT BY",
3200            into=Sort,
3201            dialect=dialect,
3202            **opts,
3203        )
3204
3205    def cluster_by(
3206        self,
3207        *expressions: t.Optional[ExpOrStr],
3208        append: bool = True,
3209        dialect: DialectType = None,
3210        copy: bool = True,
3211        **opts,
3212    ) -> Select:
3213        """
3214        Set the CLUSTER BY expression.
3215
3216        Example:
3217            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3218            'SELECT x FROM tbl CLUSTER BY x DESC'
3219
3220        Args:
3221            *expressions: the SQL code strings to parse.
3222                If a `Group` instance is passed, this is used as-is.
3223                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3224            append: if `True`, add to any existing expressions.
3225                Otherwise, this flattens all the `Order` expression into a single expression.
3226            dialect: the dialect used to parse the input expression.
3227            copy: if `False`, modify this expression instance in-place.
3228            opts: other options to use to parse the input expressions.
3229
3230        Returns:
3231            The modified Select expression.
3232        """
3233        return _apply_child_list_builder(
3234            *expressions,
3235            instance=self,
3236            arg="cluster",
3237            append=append,
3238            copy=copy,
3239            prefix="CLUSTER BY",
3240            into=Cluster,
3241            dialect=dialect,
3242            **opts,
3243        )
3244
3245    def select(
3246        self,
3247        *expressions: t.Optional[ExpOrStr],
3248        append: bool = True,
3249        dialect: DialectType = None,
3250        copy: bool = True,
3251        **opts,
3252    ) -> Select:
3253        return _apply_list_builder(
3254            *expressions,
3255            instance=self,
3256            arg="expressions",
3257            append=append,
3258            dialect=dialect,
3259            into=Expression,
3260            copy=copy,
3261            **opts,
3262        )
3263
3264    def lateral(
3265        self,
3266        *expressions: t.Optional[ExpOrStr],
3267        append: bool = True,
3268        dialect: DialectType = None,
3269        copy: bool = True,
3270        **opts,
3271    ) -> Select:
3272        """
3273        Append to or set the LATERAL expressions.
3274
3275        Example:
3276            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3277            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3278
3279        Args:
3280            *expressions: the SQL code strings to parse.
3281                If an `Expression` instance is passed, it will be used as-is.
3282            append: if `True`, add to any existing expressions.
3283                Otherwise, this resets the expressions.
3284            dialect: the dialect used to parse the input expressions.
3285            copy: if `False`, modify this expression instance in-place.
3286            opts: other options to use to parse the input expressions.
3287
3288        Returns:
3289            The modified Select expression.
3290        """
3291        return _apply_list_builder(
3292            *expressions,
3293            instance=self,
3294            arg="laterals",
3295            append=append,
3296            into=Lateral,
3297            prefix="LATERAL VIEW",
3298            dialect=dialect,
3299            copy=copy,
3300            **opts,
3301        )
3302
3303    def join(
3304        self,
3305        expression: ExpOrStr,
3306        on: t.Optional[ExpOrStr] = None,
3307        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3308        append: bool = True,
3309        join_type: t.Optional[str] = None,
3310        join_alias: t.Optional[Identifier | str] = None,
3311        dialect: DialectType = None,
3312        copy: bool = True,
3313        **opts,
3314    ) -> Select:
3315        """
3316        Append to or set the JOIN expressions.
3317
3318        Example:
3319            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3320            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3321
3322            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3323            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3324
3325            Use `join_type` to change the type of join:
3326
3327            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3328            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3329
3330        Args:
3331            expression: the SQL code string to parse.
3332                If an `Expression` instance is passed, it will be used as-is.
3333            on: optionally specify the join "on" criteria as a SQL string.
3334                If an `Expression` instance is passed, it will be used as-is.
3335            using: optionally specify the join "using" criteria as a SQL string.
3336                If an `Expression` instance is passed, it will be used as-is.
3337            append: if `True`, add to any existing expressions.
3338                Otherwise, this resets the expressions.
3339            join_type: if set, alter the parsed join type.
3340            join_alias: an optional alias for the joined source.
3341            dialect: the dialect used to parse the input expressions.
3342            copy: if `False`, modify this expression instance in-place.
3343            opts: other options to use to parse the input expressions.
3344
3345        Returns:
3346            Select: the modified expression.
3347        """
3348        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3349
3350        try:
3351            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3352        except ParseError:
3353            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3354
3355        join = expression if isinstance(expression, Join) else Join(this=expression)
3356
3357        if isinstance(join.this, Select):
3358            join.this.replace(join.this.subquery())
3359
3360        if join_type:
3361            method: t.Optional[Token]
3362            side: t.Optional[Token]
3363            kind: t.Optional[Token]
3364
3365            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3366
3367            if method:
3368                join.set("method", method.text)
3369            if side:
3370                join.set("side", side.text)
3371            if kind:
3372                join.set("kind", kind.text)
3373
3374        if on:
3375            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3376            join.set("on", on)
3377
3378        if using:
3379            join = _apply_list_builder(
3380                *ensure_list(using),
3381                instance=join,
3382                arg="using",
3383                append=append,
3384                copy=copy,
3385                into=Identifier,
3386                **opts,
3387            )
3388
3389        if join_alias:
3390            join.set("this", alias_(join.this, join_alias, table=True))
3391
3392        return _apply_list_builder(
3393            join,
3394            instance=self,
3395            arg="joins",
3396            append=append,
3397            copy=copy,
3398            **opts,
3399        )
3400
3401    def where(
3402        self,
3403        *expressions: t.Optional[ExpOrStr],
3404        append: bool = True,
3405        dialect: DialectType = None,
3406        copy: bool = True,
3407        **opts,
3408    ) -> Select:
3409        """
3410        Append to or set the WHERE expressions.
3411
3412        Example:
3413            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3414            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3415
3416        Args:
3417            *expressions: the SQL code strings to parse.
3418                If an `Expression` instance is passed, it will be used as-is.
3419                Multiple expressions are combined with an AND operator.
3420            append: if `True`, AND the new expressions to any existing expression.
3421                Otherwise, this resets the expression.
3422            dialect: the dialect used to parse the input expressions.
3423            copy: if `False`, modify this expression instance in-place.
3424            opts: other options to use to parse the input expressions.
3425
3426        Returns:
3427            Select: the modified expression.
3428        """
3429        return _apply_conjunction_builder(
3430            *expressions,
3431            instance=self,
3432            arg="where",
3433            append=append,
3434            into=Where,
3435            dialect=dialect,
3436            copy=copy,
3437            **opts,
3438        )
3439
3440    def having(
3441        self,
3442        *expressions: t.Optional[ExpOrStr],
3443        append: bool = True,
3444        dialect: DialectType = None,
3445        copy: bool = True,
3446        **opts,
3447    ) -> Select:
3448        """
3449        Append to or set the HAVING expressions.
3450
3451        Example:
3452            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3453            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3454
3455        Args:
3456            *expressions: the SQL code strings to parse.
3457                If an `Expression` instance is passed, it will be used as-is.
3458                Multiple expressions are combined with an AND operator.
3459            append: if `True`, AND the new expressions to any existing expression.
3460                Otherwise, this resets the expression.
3461            dialect: the dialect used to parse the input expressions.
3462            copy: if `False`, modify this expression instance in-place.
3463            opts: other options to use to parse the input expressions.
3464
3465        Returns:
3466            The modified Select expression.
3467        """
3468        return _apply_conjunction_builder(
3469            *expressions,
3470            instance=self,
3471            arg="having",
3472            append=append,
3473            into=Having,
3474            dialect=dialect,
3475            copy=copy,
3476            **opts,
3477        )
3478
3479    def window(
3480        self,
3481        *expressions: t.Optional[ExpOrStr],
3482        append: bool = True,
3483        dialect: DialectType = None,
3484        copy: bool = True,
3485        **opts,
3486    ) -> Select:
3487        return _apply_list_builder(
3488            *expressions,
3489            instance=self,
3490            arg="windows",
3491            append=append,
3492            into=Window,
3493            dialect=dialect,
3494            copy=copy,
3495            **opts,
3496        )
3497
3498    def qualify(
3499        self,
3500        *expressions: t.Optional[ExpOrStr],
3501        append: bool = True,
3502        dialect: DialectType = None,
3503        copy: bool = True,
3504        **opts,
3505    ) -> Select:
3506        return _apply_conjunction_builder(
3507            *expressions,
3508            instance=self,
3509            arg="qualify",
3510            append=append,
3511            into=Qualify,
3512            dialect=dialect,
3513            copy=copy,
3514            **opts,
3515        )
3516
3517    def distinct(
3518        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3519    ) -> Select:
3520        """
3521        Set the OFFSET expression.
3522
3523        Example:
3524            >>> Select().from_("tbl").select("x").distinct().sql()
3525            'SELECT DISTINCT x FROM tbl'
3526
3527        Args:
3528            ons: the expressions to distinct on
3529            distinct: whether the Select should be distinct
3530            copy: if `False`, modify this expression instance in-place.
3531
3532        Returns:
3533            Select: the modified expression.
3534        """
3535        instance = maybe_copy(self, copy)
3536        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3537        instance.set("distinct", Distinct(on=on) if distinct else None)
3538        return instance
3539
3540    def ctas(
3541        self,
3542        table: ExpOrStr,
3543        properties: t.Optional[t.Dict] = None,
3544        dialect: DialectType = None,
3545        copy: bool = True,
3546        **opts,
3547    ) -> Create:
3548        """
3549        Convert this expression to a CREATE TABLE AS statement.
3550
3551        Example:
3552            >>> Select().select("*").from_("tbl").ctas("x").sql()
3553            'CREATE TABLE x AS SELECT * FROM tbl'
3554
3555        Args:
3556            table: the SQL code string to parse as the table name.
3557                If another `Expression` instance is passed, it will be used as-is.
3558            properties: an optional mapping of table properties
3559            dialect: the dialect used to parse the input table.
3560            copy: if `False`, modify this expression instance in-place.
3561            opts: other options to use to parse the input table.
3562
3563        Returns:
3564            The new Create expression.
3565        """
3566        instance = maybe_copy(self, copy)
3567        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3568
3569        properties_expression = None
3570        if properties:
3571            properties_expression = Properties.from_dict(properties)
3572
3573        return Create(
3574            this=table_expression,
3575            kind="TABLE",
3576            expression=instance,
3577            properties=properties_expression,
3578        )
3579
3580    def lock(self, update: bool = True, copy: bool = True) -> Select:
3581        """
3582        Set the locking read mode for this expression.
3583
3584        Examples:
3585            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3586            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3587
3588            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3589            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3590
3591        Args:
3592            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3593            copy: if `False`, modify this expression instance in-place.
3594
3595        Returns:
3596            The modified expression.
3597        """
3598        inst = maybe_copy(self, copy)
3599        inst.set("locks", [Lock(update=update)])
3600
3601        return inst
3602
3603    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3604        """
3605        Set hints for this expression.
3606
3607        Examples:
3608            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3609            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3610
3611        Args:
3612            hints: The SQL code strings to parse as the hints.
3613                If an `Expression` instance is passed, it will be used as-is.
3614            dialect: The dialect used to parse the hints.
3615            copy: If `False`, modify this expression instance in-place.
3616
3617        Returns:
3618            The modified expression.
3619        """
3620        inst = maybe_copy(self, copy)
3621        inst.set(
3622            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3623        )
3624
3625        return inst
3626
3627    @property
3628    def named_selects(self) -> t.List[str]:
3629        return [e.output_name for e in self.expressions if e.alias_or_name]
3630
3631    @property
3632    def is_star(self) -> bool:
3633        return any(expression.is_star for expression in self.expressions)
3634
3635    @property
3636    def selects(self) -> t.List[Expression]:
3637        return self.expressions
3638
3639
3640UNWRAPPED_QUERIES = (Select, Union)
3641
3642
3643class Subquery(DerivedTable, Query):
3644    arg_types = {
3645        "this": True,
3646        "alias": False,
3647        "with": False,
3648        **QUERY_MODIFIERS,
3649    }
3650
3651    def unnest(self):
3652        """Returns the first non subquery."""
3653        expression = self
3654        while isinstance(expression, Subquery):
3655            expression = expression.this
3656        return expression
3657
3658    def unwrap(self) -> Subquery:
3659        expression = self
3660        while expression.same_parent and expression.is_wrapper:
3661            expression = t.cast(Subquery, expression.parent)
3662        return expression
3663
3664    def select(
3665        self,
3666        *expressions: t.Optional[ExpOrStr],
3667        append: bool = True,
3668        dialect: DialectType = None,
3669        copy: bool = True,
3670        **opts,
3671    ) -> Subquery:
3672        this = maybe_copy(self, copy)
3673        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3674        return this
3675
3676    @property
3677    def is_wrapper(self) -> bool:
3678        """
3679        Whether this Subquery acts as a simple wrapper around another expression.
3680
3681        SELECT * FROM (((SELECT * FROM t)))
3682                      ^
3683                      This corresponds to a "wrapper" Subquery node
3684        """
3685        return all(v is None for k, v in self.args.items() if k != "this")
3686
3687    @property
3688    def is_star(self) -> bool:
3689        return self.this.is_star
3690
3691    @property
3692    def output_name(self) -> str:
3693        return self.alias
3694
3695
3696class TableSample(Expression):
3697    arg_types = {
3698        "this": False,
3699        "expressions": False,
3700        "method": False,
3701        "bucket_numerator": False,
3702        "bucket_denominator": False,
3703        "bucket_field": False,
3704        "percent": False,
3705        "rows": False,
3706        "size": False,
3707        "seed": False,
3708    }
3709
3710
3711class Tag(Expression):
3712    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3713
3714    arg_types = {
3715        "this": False,
3716        "prefix": False,
3717        "postfix": False,
3718    }
3719
3720
3721# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3722# https://duckdb.org/docs/sql/statements/pivot
3723class Pivot(Expression):
3724    arg_types = {
3725        "this": False,
3726        "alias": False,
3727        "expressions": False,
3728        "field": False,
3729        "unpivot": False,
3730        "using": False,
3731        "group": False,
3732        "columns": False,
3733        "include_nulls": False,
3734    }
3735
3736    @property
3737    def unpivot(self) -> bool:
3738        return bool(self.args.get("unpivot"))
3739
3740
3741class Window(Condition):
3742    arg_types = {
3743        "this": True,
3744        "partition_by": False,
3745        "order": False,
3746        "spec": False,
3747        "alias": False,
3748        "over": False,
3749        "first": False,
3750    }
3751
3752
3753class WindowSpec(Expression):
3754    arg_types = {
3755        "kind": False,
3756        "start": False,
3757        "start_side": False,
3758        "end": False,
3759        "end_side": False,
3760    }
3761
3762
3763class PreWhere(Expression):
3764    pass
3765
3766
3767class Where(Expression):
3768    pass
3769
3770
3771class Star(Expression):
3772    arg_types = {"except": False, "replace": False}
3773
3774    @property
3775    def name(self) -> str:
3776        return "*"
3777
3778    @property
3779    def output_name(self) -> str:
3780        return self.name
3781
3782
3783class Parameter(Condition):
3784    arg_types = {"this": True, "expression": False}
3785
3786
3787class SessionParameter(Condition):
3788    arg_types = {"this": True, "kind": False}
3789
3790
3791class Placeholder(Condition):
3792    arg_types = {"this": False, "kind": False}
3793
3794    @property
3795    def name(self) -> str:
3796        return self.this or "?"
3797
3798
3799class Null(Condition):
3800    arg_types: t.Dict[str, t.Any] = {}
3801
3802    @property
3803    def name(self) -> str:
3804        return "NULL"
3805
3806
3807class Boolean(Condition):
3808    pass
3809
3810
3811class DataTypeParam(Expression):
3812    arg_types = {"this": True, "expression": False}
3813
3814    @property
3815    def name(self) -> str:
3816        return self.this.name
3817
3818
3819class DataType(Expression):
3820    arg_types = {
3821        "this": True,
3822        "expressions": False,
3823        "nested": False,
3824        "values": False,
3825        "prefix": False,
3826        "kind": False,
3827    }
3828
3829    class Type(AutoName):
3830        ARRAY = auto()
3831        AGGREGATEFUNCTION = auto()
3832        SIMPLEAGGREGATEFUNCTION = auto()
3833        BIGDECIMAL = auto()
3834        BIGINT = auto()
3835        BIGSERIAL = auto()
3836        BINARY = auto()
3837        BIT = auto()
3838        BOOLEAN = auto()
3839        BPCHAR = auto()
3840        CHAR = auto()
3841        DATE = auto()
3842        DATE32 = auto()
3843        DATEMULTIRANGE = auto()
3844        DATERANGE = auto()
3845        DATETIME = auto()
3846        DATETIME64 = auto()
3847        DECIMAL = auto()
3848        DOUBLE = auto()
3849        ENUM = auto()
3850        ENUM8 = auto()
3851        ENUM16 = auto()
3852        FIXEDSTRING = auto()
3853        FLOAT = auto()
3854        GEOGRAPHY = auto()
3855        GEOMETRY = auto()
3856        HLLSKETCH = auto()
3857        HSTORE = auto()
3858        IMAGE = auto()
3859        INET = auto()
3860        INT = auto()
3861        INT128 = auto()
3862        INT256 = auto()
3863        INT4MULTIRANGE = auto()
3864        INT4RANGE = auto()
3865        INT8MULTIRANGE = auto()
3866        INT8RANGE = auto()
3867        INTERVAL = auto()
3868        IPADDRESS = auto()
3869        IPPREFIX = auto()
3870        IPV4 = auto()
3871        IPV6 = auto()
3872        JSON = auto()
3873        JSONB = auto()
3874        LONGBLOB = auto()
3875        LONGTEXT = auto()
3876        LOWCARDINALITY = auto()
3877        MAP = auto()
3878        MEDIUMBLOB = auto()
3879        MEDIUMINT = auto()
3880        MEDIUMTEXT = auto()
3881        MONEY = auto()
3882        NAME = auto()
3883        NCHAR = auto()
3884        NESTED = auto()
3885        NULL = auto()
3886        NULLABLE = auto()
3887        NUMMULTIRANGE = auto()
3888        NUMRANGE = auto()
3889        NVARCHAR = auto()
3890        OBJECT = auto()
3891        ROWVERSION = auto()
3892        SERIAL = auto()
3893        SET = auto()
3894        SMALLINT = auto()
3895        SMALLMONEY = auto()
3896        SMALLSERIAL = auto()
3897        STRUCT = auto()
3898        SUPER = auto()
3899        TEXT = auto()
3900        TINYBLOB = auto()
3901        TINYTEXT = auto()
3902        TIME = auto()
3903        TIMETZ = auto()
3904        TIMESTAMP = auto()
3905        TIMESTAMPLTZ = auto()
3906        TIMESTAMPTZ = auto()
3907        TIMESTAMP_S = auto()
3908        TIMESTAMP_MS = auto()
3909        TIMESTAMP_NS = auto()
3910        TINYINT = auto()
3911        TSMULTIRANGE = auto()
3912        TSRANGE = auto()
3913        TSTZMULTIRANGE = auto()
3914        TSTZRANGE = auto()
3915        UBIGINT = auto()
3916        UINT = auto()
3917        UINT128 = auto()
3918        UINT256 = auto()
3919        UMEDIUMINT = auto()
3920        UDECIMAL = auto()
3921        UNIQUEIDENTIFIER = auto()
3922        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3923        USERDEFINED = "USER-DEFINED"
3924        USMALLINT = auto()
3925        UTINYINT = auto()
3926        UUID = auto()
3927        VARBINARY = auto()
3928        VARCHAR = auto()
3929        VARIANT = auto()
3930        XML = auto()
3931        YEAR = auto()
3932
3933    STRUCT_TYPES = {
3934        Type.NESTED,
3935        Type.OBJECT,
3936        Type.STRUCT,
3937    }
3938
3939    NESTED_TYPES = {
3940        *STRUCT_TYPES,
3941        Type.ARRAY,
3942        Type.MAP,
3943    }
3944
3945    TEXT_TYPES = {
3946        Type.CHAR,
3947        Type.NCHAR,
3948        Type.NVARCHAR,
3949        Type.TEXT,
3950        Type.VARCHAR,
3951        Type.NAME,
3952    }
3953
3954    SIGNED_INTEGER_TYPES = {
3955        Type.BIGINT,
3956        Type.INT,
3957        Type.INT128,
3958        Type.INT256,
3959        Type.MEDIUMINT,
3960        Type.SMALLINT,
3961        Type.TINYINT,
3962    }
3963
3964    UNSIGNED_INTEGER_TYPES = {
3965        Type.UBIGINT,
3966        Type.UINT,
3967        Type.UINT128,
3968        Type.UINT256,
3969        Type.UMEDIUMINT,
3970        Type.USMALLINT,
3971        Type.UTINYINT,
3972    }
3973
3974    INTEGER_TYPES = {
3975        *SIGNED_INTEGER_TYPES,
3976        *UNSIGNED_INTEGER_TYPES,
3977        Type.BIT,
3978    }
3979
3980    FLOAT_TYPES = {
3981        Type.DOUBLE,
3982        Type.FLOAT,
3983    }
3984
3985    REAL_TYPES = {
3986        *FLOAT_TYPES,
3987        Type.BIGDECIMAL,
3988        Type.DECIMAL,
3989        Type.MONEY,
3990        Type.SMALLMONEY,
3991        Type.UDECIMAL,
3992    }
3993
3994    NUMERIC_TYPES = {
3995        *INTEGER_TYPES,
3996        *REAL_TYPES,
3997    }
3998
3999    TEMPORAL_TYPES = {
4000        Type.DATE,
4001        Type.DATE32,
4002        Type.DATETIME,
4003        Type.DATETIME64,
4004        Type.TIME,
4005        Type.TIMESTAMP,
4006        Type.TIMESTAMPLTZ,
4007        Type.TIMESTAMPTZ,
4008        Type.TIMESTAMP_MS,
4009        Type.TIMESTAMP_NS,
4010        Type.TIMESTAMP_S,
4011        Type.TIMETZ,
4012    }
4013
4014    @classmethod
4015    def build(
4016        cls,
4017        dtype: DATA_TYPE,
4018        dialect: DialectType = None,
4019        udt: bool = False,
4020        copy: bool = True,
4021        **kwargs,
4022    ) -> DataType:
4023        """
4024        Constructs a DataType object.
4025
4026        Args:
4027            dtype: the data type of interest.
4028            dialect: the dialect to use for parsing `dtype`, in case it's a string.
4029            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
4030                DataType, thus creating a user-defined type.
4031            copy: whether to copy the data type.
4032            kwargs: additional arguments to pass in the constructor of DataType.
4033
4034        Returns:
4035            The constructed DataType object.
4036        """
4037        from sqlglot import parse_one
4038
4039        if isinstance(dtype, str):
4040            if dtype.upper() == "UNKNOWN":
4041                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
4042
4043            try:
4044                data_type_exp = parse_one(
4045                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
4046                )
4047            except ParseError:
4048                if udt:
4049                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
4050                raise
4051        elif isinstance(dtype, DataType.Type):
4052            data_type_exp = DataType(this=dtype)
4053        elif isinstance(dtype, DataType):
4054            return maybe_copy(dtype, copy)
4055        else:
4056            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
4057
4058        return DataType(**{**data_type_exp.args, **kwargs})
4059
4060    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4061        """
4062        Checks whether this DataType matches one of the provided data types. Nested types or precision
4063        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
4064
4065        Args:
4066            dtypes: the data types to compare this DataType to.
4067
4068        Returns:
4069            True, if and only if there is a type in `dtypes` which is equal to this DataType.
4070        """
4071        for dtype in dtypes:
4072            other = DataType.build(dtype, copy=False, udt=True)
4073
4074            if (
4075                other.expressions
4076                or self.this == DataType.Type.USERDEFINED
4077                or other.this == DataType.Type.USERDEFINED
4078            ):
4079                matches = self == other
4080            else:
4081                matches = self.this == other.this
4082
4083            if matches:
4084                return True
4085        return False
4086
4087
4088DATA_TYPE = t.Union[str, DataType, DataType.Type]
4089
4090
4091# https://www.postgresql.org/docs/15/datatype-pseudo.html
4092class PseudoType(DataType):
4093    arg_types = {"this": True}
4094
4095
4096# https://www.postgresql.org/docs/15/datatype-oid.html
4097class ObjectIdentifier(DataType):
4098    arg_types = {"this": True}
4099
4100
4101# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
4102class SubqueryPredicate(Predicate):
4103    pass
4104
4105
4106class All(SubqueryPredicate):
4107    pass
4108
4109
4110class Any(SubqueryPredicate):
4111    pass
4112
4113
4114class Exists(SubqueryPredicate):
4115    pass
4116
4117
4118# Commands to interact with the databases or engines. For most of the command
4119# expressions we parse whatever comes after the command's name as a string.
4120class Command(Expression):
4121    arg_types = {"this": True, "expression": False}
4122
4123
4124class Transaction(Expression):
4125    arg_types = {"this": False, "modes": False, "mark": False}
4126
4127
4128class Commit(Expression):
4129    arg_types = {"chain": False, "this": False, "durability": False}
4130
4131
4132class Rollback(Expression):
4133    arg_types = {"savepoint": False, "this": False}
4134
4135
4136class AlterTable(Expression):
4137    arg_types = {
4138        "this": True,
4139        "actions": True,
4140        "exists": False,
4141        "only": False,
4142        "options": False,
4143    }
4144
4145
4146class AddConstraint(Expression):
4147    arg_types = {"expressions": True}
4148
4149
4150class DropPartition(Expression):
4151    arg_types = {"expressions": True, "exists": False}
4152
4153
4154# Binary expressions like (ADD a b)
4155class Binary(Condition):
4156    arg_types = {"this": True, "expression": True}
4157
4158    @property
4159    def left(self) -> Expression:
4160        return self.this
4161
4162    @property
4163    def right(self) -> Expression:
4164        return self.expression
4165
4166
4167class Add(Binary):
4168    pass
4169
4170
4171class Connector(Binary):
4172    pass
4173
4174
4175class And(Connector):
4176    pass
4177
4178
4179class Or(Connector):
4180    pass
4181
4182
4183class BitwiseAnd(Binary):
4184    pass
4185
4186
4187class BitwiseLeftShift(Binary):
4188    pass
4189
4190
4191class BitwiseOr(Binary):
4192    pass
4193
4194
4195class BitwiseRightShift(Binary):
4196    pass
4197
4198
4199class BitwiseXor(Binary):
4200    pass
4201
4202
4203class Div(Binary):
4204    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
4205
4206
4207class Overlaps(Binary):
4208    pass
4209
4210
4211class Dot(Binary):
4212    @property
4213    def is_star(self) -> bool:
4214        return self.expression.is_star
4215
4216    @property
4217    def name(self) -> str:
4218        return self.expression.name
4219
4220    @property
4221    def output_name(self) -> str:
4222        return self.name
4223
4224    @classmethod
4225    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4226        """Build a Dot object with a sequence of expressions."""
4227        if len(expressions) < 2:
4228            raise ValueError("Dot requires >= 2 expressions.")
4229
4230        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4231
4232    @property
4233    def parts(self) -> t.List[Expression]:
4234        """Return the parts of a table / column in order catalog, db, table."""
4235        this, *parts = self.flatten()
4236
4237        parts.reverse()
4238
4239        for arg in COLUMN_PARTS:
4240            part = this.args.get(arg)
4241
4242            if isinstance(part, Expression):
4243                parts.append(part)
4244
4245        parts.reverse()
4246        return parts
4247
4248
4249class DPipe(Binary):
4250    arg_types = {"this": True, "expression": True, "safe": False}
4251
4252
4253class EQ(Binary, Predicate):
4254    pass
4255
4256
4257class NullSafeEQ(Binary, Predicate):
4258    pass
4259
4260
4261class NullSafeNEQ(Binary, Predicate):
4262    pass
4263
4264
4265# Represents e.g. := in DuckDB which is mostly used for setting parameters
4266class PropertyEQ(Binary):
4267    pass
4268
4269
4270class Distance(Binary):
4271    pass
4272
4273
4274class Escape(Binary):
4275    pass
4276
4277
4278class Glob(Binary, Predicate):
4279    pass
4280
4281
4282class GT(Binary, Predicate):
4283    pass
4284
4285
4286class GTE(Binary, Predicate):
4287    pass
4288
4289
4290class ILike(Binary, Predicate):
4291    pass
4292
4293
4294class ILikeAny(Binary, Predicate):
4295    pass
4296
4297
4298class IntDiv(Binary):
4299    pass
4300
4301
4302class Is(Binary, Predicate):
4303    pass
4304
4305
4306class Kwarg(Binary):
4307    """Kwarg in special functions like func(kwarg => y)."""
4308
4309
4310class Like(Binary, Predicate):
4311    pass
4312
4313
4314class LikeAny(Binary, Predicate):
4315    pass
4316
4317
4318class LT(Binary, Predicate):
4319    pass
4320
4321
4322class LTE(Binary, Predicate):
4323    pass
4324
4325
4326class Mod(Binary):
4327    pass
4328
4329
4330class Mul(Binary):
4331    pass
4332
4333
4334class NEQ(Binary, Predicate):
4335    pass
4336
4337
4338# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4339class Operator(Binary):
4340    arg_types = {"this": True, "operator": True, "expression": True}
4341
4342
4343class SimilarTo(Binary, Predicate):
4344    pass
4345
4346
4347class Slice(Binary):
4348    arg_types = {"this": False, "expression": False}
4349
4350
4351class Sub(Binary):
4352    pass
4353
4354
4355# Unary Expressions
4356# (NOT a)
4357class Unary(Condition):
4358    pass
4359
4360
4361class BitwiseNot(Unary):
4362    pass
4363
4364
4365class Not(Unary):
4366    pass
4367
4368
4369class Paren(Unary):
4370    @property
4371    def output_name(self) -> str:
4372        return self.this.name
4373
4374
4375class Neg(Unary):
4376    pass
4377
4378
4379class Alias(Expression):
4380    arg_types = {"this": True, "alias": False}
4381
4382    @property
4383    def output_name(self) -> str:
4384        return self.alias
4385
4386
4387# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4388# other dialects require identifiers. This enables us to transpile between them easily.
4389class PivotAlias(Alias):
4390    pass
4391
4392
4393class Aliases(Expression):
4394    arg_types = {"this": True, "expressions": True}
4395
4396    @property
4397    def aliases(self):
4398        return self.expressions
4399
4400
4401# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4402class AtIndex(Expression):
4403    arg_types = {"this": True, "expression": True}
4404
4405
4406class AtTimeZone(Expression):
4407    arg_types = {"this": True, "zone": True}
4408
4409
4410class FromTimeZone(Expression):
4411    arg_types = {"this": True, "zone": True}
4412
4413
4414class Between(Predicate):
4415    arg_types = {"this": True, "low": True, "high": True}
4416
4417
4418class Bracket(Condition):
4419    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4420    arg_types = {
4421        "this": True,
4422        "expressions": True,
4423        "offset": False,
4424        "safe": False,
4425        "returns_list_for_maps": False,
4426    }
4427
4428    @property
4429    def output_name(self) -> str:
4430        if len(self.expressions) == 1:
4431            return self.expressions[0].output_name
4432
4433        return super().output_name
4434
4435
4436class Distinct(Expression):
4437    arg_types = {"expressions": False, "on": False}
4438
4439
4440class In(Predicate):
4441    arg_types = {
4442        "this": True,
4443        "expressions": False,
4444        "query": False,
4445        "unnest": False,
4446        "field": False,
4447        "is_global": False,
4448    }
4449
4450
4451# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4452class ForIn(Expression):
4453    arg_types = {"this": True, "expression": True}
4454
4455
4456class TimeUnit(Expression):
4457    """Automatically converts unit arg into a var."""
4458
4459    arg_types = {"unit": False}
4460
4461    UNABBREVIATED_UNIT_NAME = {
4462        "D": "DAY",
4463        "H": "HOUR",
4464        "M": "MINUTE",
4465        "MS": "MILLISECOND",
4466        "NS": "NANOSECOND",
4467        "Q": "QUARTER",
4468        "S": "SECOND",
4469        "US": "MICROSECOND",
4470        "W": "WEEK",
4471        "Y": "YEAR",
4472    }
4473
4474    VAR_LIKE = (Column, Literal, Var)
4475
4476    def __init__(self, **args):
4477        unit = args.get("unit")
4478        if isinstance(unit, self.VAR_LIKE):
4479            args["unit"] = Var(
4480                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4481            )
4482        elif isinstance(unit, Week):
4483            unit.set("this", Var(this=unit.this.name.upper()))
4484
4485        super().__init__(**args)
4486
4487    @property
4488    def unit(self) -> t.Optional[Var | IntervalSpan]:
4489        return self.args.get("unit")
4490
4491
4492class IntervalOp(TimeUnit):
4493    arg_types = {"unit": True, "expression": True}
4494
4495    def interval(self):
4496        return Interval(
4497            this=self.expression.copy(),
4498            unit=self.unit.copy(),
4499        )
4500
4501
4502# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4503# https://trino.io/docs/current/language/types.html#interval-day-to-second
4504# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4505class IntervalSpan(DataType):
4506    arg_types = {"this": True, "expression": True}
4507
4508
4509class Interval(TimeUnit):
4510    arg_types = {"this": False, "unit": False}
4511
4512
4513class IgnoreNulls(Expression):
4514    pass
4515
4516
4517class RespectNulls(Expression):
4518    pass
4519
4520
4521# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4522class HavingMax(Expression):
4523    arg_types = {"this": True, "expression": True, "max": True}
4524
4525
4526# Functions
4527class Func(Condition):
4528    """
4529    The base class for all function expressions.
4530
4531    Attributes:
4532        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4533            treated as a variable length argument and the argument's value will be stored as a list.
4534        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4535            function expression. These values are used to map this node to a name during parsing as
4536            well as to provide the function's name during SQL string generation. By default the SQL
4537            name is set to the expression's class name transformed to snake case.
4538    """
4539
4540    is_var_len_args = False
4541
4542    @classmethod
4543    def from_arg_list(cls, args):
4544        if cls.is_var_len_args:
4545            all_arg_keys = list(cls.arg_types)
4546            # If this function supports variable length argument treat the last argument as such.
4547            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4548            num_non_var = len(non_var_len_arg_keys)
4549
4550            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4551            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4552        else:
4553            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4554
4555        return cls(**args_dict)
4556
4557    @classmethod
4558    def sql_names(cls):
4559        if cls is Func:
4560            raise NotImplementedError(
4561                "SQL name is only supported by concrete function implementations"
4562            )
4563        if "_sql_names" not in cls.__dict__:
4564            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4565        return cls._sql_names
4566
4567    @classmethod
4568    def sql_name(cls):
4569        return cls.sql_names()[0]
4570
4571    @classmethod
4572    def default_parser_mappings(cls):
4573        return {name: cls.from_arg_list for name in cls.sql_names()}
4574
4575
4576class AggFunc(Func):
4577    pass
4578
4579
4580class ParameterizedAgg(AggFunc):
4581    arg_types = {"this": True, "expressions": True, "params": True}
4582
4583
4584class Abs(Func):
4585    pass
4586
4587
4588class ArgMax(AggFunc):
4589    arg_types = {"this": True, "expression": True, "count": False}
4590    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4591
4592
4593class ArgMin(AggFunc):
4594    arg_types = {"this": True, "expression": True, "count": False}
4595    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4596
4597
4598class ApproxTopK(AggFunc):
4599    arg_types = {"this": True, "expression": False, "counters": False}
4600
4601
4602class Flatten(Func):
4603    pass
4604
4605
4606# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4607class Transform(Func):
4608    arg_types = {"this": True, "expression": True}
4609
4610
4611class Anonymous(Func):
4612    arg_types = {"this": True, "expressions": False}
4613    is_var_len_args = True
4614
4615    @property
4616    def name(self) -> str:
4617        return self.this if isinstance(self.this, str) else self.this.name
4618
4619
4620class AnonymousAggFunc(AggFunc):
4621    arg_types = {"this": True, "expressions": False}
4622    is_var_len_args = True
4623
4624
4625# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4626class CombinedAggFunc(AnonymousAggFunc):
4627    arg_types = {"this": True, "expressions": False, "parts": True}
4628
4629
4630class CombinedParameterizedAgg(ParameterizedAgg):
4631    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4632
4633
4634# https://docs.snowflake.com/en/sql-reference/functions/hll
4635# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4636class Hll(AggFunc):
4637    arg_types = {"this": True, "expressions": False}
4638    is_var_len_args = True
4639
4640
4641class ApproxDistinct(AggFunc):
4642    arg_types = {"this": True, "accuracy": False}
4643    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4644
4645
4646class Array(Func):
4647    arg_types = {"expressions": False}
4648    is_var_len_args = True
4649
4650
4651# https://docs.snowflake.com/en/sql-reference/functions/to_array
4652class ToArray(Func):
4653    pass
4654
4655
4656# https://docs.snowflake.com/en/sql-reference/functions/to_char
4657# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4658class ToChar(Func):
4659    arg_types = {"this": True, "format": False, "nlsparam": False}
4660
4661
4662# https://docs.snowflake.com/en/sql-reference/functions/to_decimal
4663# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_NUMBER.html
4664class ToNumber(Func):
4665    arg_types = {
4666        "this": True,
4667        "format": False,
4668        "nlsparam": False,
4669        "precision": False,
4670        "scale": False,
4671    }
4672
4673
4674# https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
4675class Convert(Func):
4676    arg_types = {"this": True, "expression": True, "style": False}
4677
4678
4679class GenerateSeries(Func):
4680    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
4681
4682
4683class ArrayAgg(AggFunc):
4684    pass
4685
4686
4687class ArrayUniqueAgg(AggFunc):
4688    pass
4689
4690
4691class ArrayAll(Func):
4692    arg_types = {"this": True, "expression": True}
4693
4694
4695# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4696class ArrayAny(Func):
4697    arg_types = {"this": True, "expression": True}
4698
4699
4700class ArrayConcat(Func):
4701    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4702    arg_types = {"this": True, "expressions": False}
4703    is_var_len_args = True
4704
4705
4706class ArrayContains(Binary, Func):
4707    pass
4708
4709
4710class ArrayContained(Binary):
4711    pass
4712
4713
4714class ArrayFilter(Func):
4715    arg_types = {"this": True, "expression": True}
4716    _sql_names = ["FILTER", "ARRAY_FILTER"]
4717
4718
4719class ArrayToString(Func):
4720    arg_types = {"this": True, "expression": True, "null": False}
4721    _sql_names = ["ARRAY_TO_STRING", "ARRAY_JOIN"]
4722
4723
4724class ArrayOverlaps(Binary, Func):
4725    pass
4726
4727
4728class ArraySize(Func):
4729    arg_types = {"this": True, "expression": False}
4730    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
4731
4732
4733class ArraySort(Func):
4734    arg_types = {"this": True, "expression": False}
4735
4736
4737class ArraySum(Func):
4738    arg_types = {"this": True, "expression": False}
4739
4740
4741class ArrayUnionAgg(AggFunc):
4742    pass
4743
4744
4745class Avg(AggFunc):
4746    pass
4747
4748
4749class AnyValue(AggFunc):
4750    pass
4751
4752
4753class Lag(AggFunc):
4754    arg_types = {"this": True, "offset": False, "default": False}
4755
4756
4757class Lead(AggFunc):
4758    arg_types = {"this": True, "offset": False, "default": False}
4759
4760
4761# some dialects have a distinction between first and first_value, usually first is an aggregate func
4762# and first_value is a window func
4763class First(AggFunc):
4764    pass
4765
4766
4767class Last(AggFunc):
4768    pass
4769
4770
4771class FirstValue(AggFunc):
4772    pass
4773
4774
4775class LastValue(AggFunc):
4776    pass
4777
4778
4779class NthValue(AggFunc):
4780    arg_types = {"this": True, "offset": True}
4781
4782
4783class Case(Func):
4784    arg_types = {"this": False, "ifs": True, "default": False}
4785
4786    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4787        instance = maybe_copy(self, copy)
4788        instance.append(
4789            "ifs",
4790            If(
4791                this=maybe_parse(condition, copy=copy, **opts),
4792                true=maybe_parse(then, copy=copy, **opts),
4793            ),
4794        )
4795        return instance
4796
4797    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4798        instance = maybe_copy(self, copy)
4799        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4800        return instance
4801
4802
4803class Cast(Func):
4804    arg_types = {
4805        "this": True,
4806        "to": True,
4807        "format": False,
4808        "safe": False,
4809        "action": False,
4810    }
4811
4812    @property
4813    def name(self) -> str:
4814        return self.this.name
4815
4816    @property
4817    def to(self) -> DataType:
4818        return self.args["to"]
4819
4820    @property
4821    def output_name(self) -> str:
4822        return self.name
4823
4824    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4825        """
4826        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4827        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4828        array<int> != array<float>.
4829
4830        Args:
4831            dtypes: the data types to compare this Cast's DataType to.
4832
4833        Returns:
4834            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4835        """
4836        return self.to.is_type(*dtypes)
4837
4838
4839class TryCast(Cast):
4840    pass
4841
4842
4843class CastToStrType(Func):
4844    arg_types = {"this": True, "to": True}
4845
4846
4847class Collate(Binary, Func):
4848    pass
4849
4850
4851class Ceil(Func):
4852    arg_types = {"this": True, "decimals": False}
4853    _sql_names = ["CEIL", "CEILING"]
4854
4855
4856class Coalesce(Func):
4857    arg_types = {"this": True, "expressions": False}
4858    is_var_len_args = True
4859    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4860
4861
4862class Chr(Func):
4863    arg_types = {"this": True, "charset": False, "expressions": False}
4864    is_var_len_args = True
4865    _sql_names = ["CHR", "CHAR"]
4866
4867
4868class Concat(Func):
4869    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4870    is_var_len_args = True
4871
4872
4873class ConcatWs(Concat):
4874    _sql_names = ["CONCAT_WS"]
4875
4876
4877# https://docs.oracle.com/cd/B13789_01/server.101/b10759/operators004.htm#i1035022
4878class ConnectByRoot(Func):
4879    pass
4880
4881
4882class Count(AggFunc):
4883    arg_types = {"this": False, "expressions": False}
4884    is_var_len_args = True
4885
4886
4887class CountIf(AggFunc):
4888    _sql_names = ["COUNT_IF", "COUNTIF"]
4889
4890
4891# cube root
4892class Cbrt(Func):
4893    pass
4894
4895
4896class CurrentDate(Func):
4897    arg_types = {"this": False}
4898
4899
4900class CurrentDatetime(Func):
4901    arg_types = {"this": False}
4902
4903
4904class CurrentTime(Func):
4905    arg_types = {"this": False}
4906
4907
4908class CurrentTimestamp(Func):
4909    arg_types = {"this": False, "transaction": False}
4910
4911
4912class CurrentUser(Func):
4913    arg_types = {"this": False}
4914
4915
4916class DateAdd(Func, IntervalOp):
4917    arg_types = {"this": True, "expression": True, "unit": False}
4918
4919
4920class DateSub(Func, IntervalOp):
4921    arg_types = {"this": True, "expression": True, "unit": False}
4922
4923
4924class DateDiff(Func, TimeUnit):
4925    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4926    arg_types = {"this": True, "expression": True, "unit": False}
4927
4928
4929class DateTrunc(Func):
4930    arg_types = {"unit": True, "this": True, "zone": False}
4931
4932    def __init__(self, **args):
4933        unit = args.get("unit")
4934        if isinstance(unit, TimeUnit.VAR_LIKE):
4935            args["unit"] = Literal.string(
4936                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4937            )
4938        elif isinstance(unit, Week):
4939            unit.set("this", Literal.string(unit.this.name.upper()))
4940
4941        super().__init__(**args)
4942
4943    @property
4944    def unit(self) -> Expression:
4945        return self.args["unit"]
4946
4947
4948class DatetimeAdd(Func, IntervalOp):
4949    arg_types = {"this": True, "expression": True, "unit": False}
4950
4951
4952class DatetimeSub(Func, IntervalOp):
4953    arg_types = {"this": True, "expression": True, "unit": False}
4954
4955
4956class DatetimeDiff(Func, TimeUnit):
4957    arg_types = {"this": True, "expression": True, "unit": False}
4958
4959
4960class DatetimeTrunc(Func, TimeUnit):
4961    arg_types = {"this": True, "unit": True, "zone": False}
4962
4963
4964class DayOfWeek(Func):
4965    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4966
4967
4968class DayOfMonth(Func):
4969    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4970
4971
4972class DayOfYear(Func):
4973    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4974
4975
4976class ToDays(Func):
4977    pass
4978
4979
4980class WeekOfYear(Func):
4981    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4982
4983
4984class MonthsBetween(Func):
4985    arg_types = {"this": True, "expression": True, "roundoff": False}
4986
4987
4988class LastDay(Func, TimeUnit):
4989    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
4990    arg_types = {"this": True, "unit": False}
4991
4992
4993class Extract(Func):
4994    arg_types = {"this": True, "expression": True}
4995
4996
4997class Timestamp(Func):
4998    arg_types = {"this": False, "expression": False, "with_tz": False}
4999
5000
5001class TimestampAdd(Func, TimeUnit):
5002    arg_types = {"this": True, "expression": True, "unit": False}
5003
5004
5005class TimestampSub(Func, TimeUnit):
5006    arg_types = {"this": True, "expression": True, "unit": False}
5007
5008
5009class TimestampDiff(Func, TimeUnit):
5010    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
5011    arg_types = {"this": True, "expression": True, "unit": False}
5012
5013
5014class TimestampTrunc(Func, TimeUnit):
5015    arg_types = {"this": True, "unit": True, "zone": False}
5016
5017
5018class TimeAdd(Func, TimeUnit):
5019    arg_types = {"this": True, "expression": True, "unit": False}
5020
5021
5022class TimeSub(Func, TimeUnit):
5023    arg_types = {"this": True, "expression": True, "unit": False}
5024
5025
5026class TimeDiff(Func, TimeUnit):
5027    arg_types = {"this": True, "expression": True, "unit": False}
5028
5029
5030class TimeTrunc(Func, TimeUnit):
5031    arg_types = {"this": True, "unit": True, "zone": False}
5032
5033
5034class DateFromParts(Func):
5035    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
5036    arg_types = {"year": True, "month": True, "day": True}
5037
5038
5039class TimeFromParts(Func):
5040    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
5041    arg_types = {
5042        "hour": True,
5043        "min": True,
5044        "sec": True,
5045        "nano": False,
5046        "fractions": False,
5047        "precision": False,
5048    }
5049
5050
5051class DateStrToDate(Func):
5052    pass
5053
5054
5055class DateToDateStr(Func):
5056    pass
5057
5058
5059class DateToDi(Func):
5060    pass
5061
5062
5063# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
5064class Date(Func):
5065    arg_types = {"this": False, "zone": False, "expressions": False}
5066    is_var_len_args = True
5067
5068
5069class Day(Func):
5070    pass
5071
5072
5073class Decode(Func):
5074    arg_types = {"this": True, "charset": True, "replace": False}
5075
5076
5077class DiToDate(Func):
5078    pass
5079
5080
5081class Encode(Func):
5082    arg_types = {"this": True, "charset": True}
5083
5084
5085class Exp(Func):
5086    pass
5087
5088
5089# https://docs.snowflake.com/en/sql-reference/functions/flatten
5090class Explode(Func):
5091    arg_types = {"this": True, "expressions": False}
5092    is_var_len_args = True
5093
5094
5095class ExplodeOuter(Explode):
5096    pass
5097
5098
5099class Posexplode(Explode):
5100    pass
5101
5102
5103class PosexplodeOuter(Posexplode, ExplodeOuter):
5104    pass
5105
5106
5107class Floor(Func):
5108    arg_types = {"this": True, "decimals": False}
5109
5110
5111class FromBase64(Func):
5112    pass
5113
5114
5115class ToBase64(Func):
5116    pass
5117
5118
5119class GenerateDateArray(Func):
5120    arg_types = {"start": True, "end": True, "interval": False}
5121
5122
5123class Greatest(Func):
5124    arg_types = {"this": True, "expressions": False}
5125    is_var_len_args = True
5126
5127
5128class GroupConcat(AggFunc):
5129    arg_types = {"this": True, "separator": False}
5130
5131
5132class Hex(Func):
5133    pass
5134
5135
5136class Xor(Connector, Func):
5137    arg_types = {"this": False, "expression": False, "expressions": False}
5138
5139
5140class If(Func):
5141    arg_types = {"this": True, "true": True, "false": False}
5142    _sql_names = ["IF", "IIF"]
5143
5144
5145class Nullif(Func):
5146    arg_types = {"this": True, "expression": True}
5147
5148
5149class Initcap(Func):
5150    arg_types = {"this": True, "expression": False}
5151
5152
5153class IsNan(Func):
5154    _sql_names = ["IS_NAN", "ISNAN"]
5155
5156
5157class IsInf(Func):
5158    _sql_names = ["IS_INF", "ISINF"]
5159
5160
5161class JSONPath(Expression):
5162    arg_types = {"expressions": True}
5163
5164    @property
5165    def output_name(self) -> str:
5166        last_segment = self.expressions[-1].this
5167        return last_segment if isinstance(last_segment, str) else ""
5168
5169
5170class JSONPathPart(Expression):
5171    arg_types = {}
5172
5173
5174class JSONPathFilter(JSONPathPart):
5175    arg_types = {"this": True}
5176
5177
5178class JSONPathKey(JSONPathPart):
5179    arg_types = {"this": True}
5180
5181
5182class JSONPathRecursive(JSONPathPart):
5183    arg_types = {"this": False}
5184
5185
5186class JSONPathRoot(JSONPathPart):
5187    pass
5188
5189
5190class JSONPathScript(JSONPathPart):
5191    arg_types = {"this": True}
5192
5193
5194class JSONPathSlice(JSONPathPart):
5195    arg_types = {"start": False, "end": False, "step": False}
5196
5197
5198class JSONPathSelector(JSONPathPart):
5199    arg_types = {"this": True}
5200
5201
5202class JSONPathSubscript(JSONPathPart):
5203    arg_types = {"this": True}
5204
5205
5206class JSONPathUnion(JSONPathPart):
5207    arg_types = {"expressions": True}
5208
5209
5210class JSONPathWildcard(JSONPathPart):
5211    pass
5212
5213
5214class FormatJson(Expression):
5215    pass
5216
5217
5218class JSONKeyValue(Expression):
5219    arg_types = {"this": True, "expression": True}
5220
5221
5222class JSONObject(Func):
5223    arg_types = {
5224        "expressions": False,
5225        "null_handling": False,
5226        "unique_keys": False,
5227        "return_type": False,
5228        "encoding": False,
5229    }
5230
5231
5232class JSONObjectAgg(AggFunc):
5233    arg_types = {
5234        "expressions": False,
5235        "null_handling": False,
5236        "unique_keys": False,
5237        "return_type": False,
5238        "encoding": False,
5239    }
5240
5241
5242# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
5243class JSONArray(Func):
5244    arg_types = {
5245        "expressions": True,
5246        "null_handling": False,
5247        "return_type": False,
5248        "strict": False,
5249    }
5250
5251
5252# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
5253class JSONArrayAgg(Func):
5254    arg_types = {
5255        "this": True,
5256        "order": False,
5257        "null_handling": False,
5258        "return_type": False,
5259        "strict": False,
5260    }
5261
5262
5263# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5264# Note: parsing of JSON column definitions is currently incomplete.
5265class JSONColumnDef(Expression):
5266    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
5267
5268
5269class JSONSchema(Expression):
5270    arg_types = {"expressions": True}
5271
5272
5273# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5274class JSONTable(Func):
5275    arg_types = {
5276        "this": True,
5277        "schema": True,
5278        "path": False,
5279        "error_handling": False,
5280        "empty_handling": False,
5281    }
5282
5283
5284class OpenJSONColumnDef(Expression):
5285    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
5286
5287
5288class OpenJSON(Func):
5289    arg_types = {"this": True, "path": False, "expressions": False}
5290
5291
5292class JSONBContains(Binary):
5293    _sql_names = ["JSONB_CONTAINS"]
5294
5295
5296class JSONExtract(Binary, Func):
5297    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5298    _sql_names = ["JSON_EXTRACT"]
5299    is_var_len_args = True
5300
5301    @property
5302    def output_name(self) -> str:
5303        return self.expression.output_name if not self.expressions else ""
5304
5305
5306class JSONExtractScalar(Binary, Func):
5307    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5308    _sql_names = ["JSON_EXTRACT_SCALAR"]
5309    is_var_len_args = True
5310
5311    @property
5312    def output_name(self) -> str:
5313        return self.expression.output_name
5314
5315
5316class JSONBExtract(Binary, Func):
5317    _sql_names = ["JSONB_EXTRACT"]
5318
5319
5320class JSONBExtractScalar(Binary, Func):
5321    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5322
5323
5324class JSONFormat(Func):
5325    arg_types = {"this": False, "options": False}
5326    _sql_names = ["JSON_FORMAT"]
5327
5328
5329# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5330class JSONArrayContains(Binary, Predicate, Func):
5331    _sql_names = ["JSON_ARRAY_CONTAINS"]
5332
5333
5334class ParseJSON(Func):
5335    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5336    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5337    arg_types = {"this": True, "expressions": False}
5338    is_var_len_args = True
5339
5340
5341class Least(Func):
5342    arg_types = {"this": True, "expressions": False}
5343    is_var_len_args = True
5344
5345
5346class Left(Func):
5347    arg_types = {"this": True, "expression": True}
5348
5349
5350class Right(Func):
5351    arg_types = {"this": True, "expression": True}
5352
5353
5354class Length(Func):
5355    _sql_names = ["LENGTH", "LEN"]
5356
5357
5358class Levenshtein(Func):
5359    arg_types = {
5360        "this": True,
5361        "expression": False,
5362        "ins_cost": False,
5363        "del_cost": False,
5364        "sub_cost": False,
5365    }
5366
5367
5368class Ln(Func):
5369    pass
5370
5371
5372class Log(Func):
5373    arg_types = {"this": True, "expression": False}
5374
5375
5376class LogicalOr(AggFunc):
5377    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5378
5379
5380class LogicalAnd(AggFunc):
5381    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5382
5383
5384class Lower(Func):
5385    _sql_names = ["LOWER", "LCASE"]
5386
5387
5388class Map(Func):
5389    arg_types = {"keys": False, "values": False}
5390
5391    @property
5392    def keys(self) -> t.List[Expression]:
5393        keys = self.args.get("keys")
5394        return keys.expressions if keys else []
5395
5396    @property
5397    def values(self) -> t.List[Expression]:
5398        values = self.args.get("values")
5399        return values.expressions if values else []
5400
5401
5402# Represents the MAP {...} syntax in DuckDB - basically convert a struct to a MAP
5403class ToMap(Func):
5404    pass
5405
5406
5407class MapFromEntries(Func):
5408    pass
5409
5410
5411class StarMap(Func):
5412    pass
5413
5414
5415class VarMap(Func):
5416    arg_types = {"keys": True, "values": True}
5417    is_var_len_args = True
5418
5419    @property
5420    def keys(self) -> t.List[Expression]:
5421        return self.args["keys"].expressions
5422
5423    @property
5424    def values(self) -> t.List[Expression]:
5425        return self.args["values"].expressions
5426
5427
5428# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5429class MatchAgainst(Func):
5430    arg_types = {"this": True, "expressions": True, "modifier": False}
5431
5432
5433class Max(AggFunc):
5434    arg_types = {"this": True, "expressions": False}
5435    is_var_len_args = True
5436
5437
5438class MD5(Func):
5439    _sql_names = ["MD5"]
5440
5441
5442# Represents the variant of the MD5 function that returns a binary value
5443class MD5Digest(Func):
5444    _sql_names = ["MD5_DIGEST"]
5445
5446
5447class Min(AggFunc):
5448    arg_types = {"this": True, "expressions": False}
5449    is_var_len_args = True
5450
5451
5452class Month(Func):
5453    pass
5454
5455
5456class AddMonths(Func):
5457    arg_types = {"this": True, "expression": True}
5458
5459
5460class Nvl2(Func):
5461    arg_types = {"this": True, "true": True, "false": False}
5462
5463
5464# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5465class Predict(Func):
5466    arg_types = {"this": True, "expression": True, "params_struct": False}
5467
5468
5469class Pow(Binary, Func):
5470    _sql_names = ["POWER", "POW"]
5471
5472
5473class PercentileCont(AggFunc):
5474    arg_types = {"this": True, "expression": False}
5475
5476
5477class PercentileDisc(AggFunc):
5478    arg_types = {"this": True, "expression": False}
5479
5480
5481class Quantile(AggFunc):
5482    arg_types = {"this": True, "quantile": True}
5483
5484
5485class ApproxQuantile(Quantile):
5486    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5487
5488
5489class Quarter(Func):
5490    pass
5491
5492
5493class Rand(Func):
5494    _sql_names = ["RAND", "RANDOM"]
5495    arg_types = {"this": False}
5496
5497
5498class Randn(Func):
5499    arg_types = {"this": False}
5500
5501
5502class RangeN(Func):
5503    arg_types = {"this": True, "expressions": True, "each": False}
5504
5505
5506class ReadCSV(Func):
5507    _sql_names = ["READ_CSV"]
5508    is_var_len_args = True
5509    arg_types = {"this": True, "expressions": False}
5510
5511
5512class Reduce(Func):
5513    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5514
5515
5516class RegexpExtract(Func):
5517    arg_types = {
5518        "this": True,
5519        "expression": True,
5520        "position": False,
5521        "occurrence": False,
5522        "parameters": False,
5523        "group": False,
5524    }
5525
5526
5527class RegexpReplace(Func):
5528    arg_types = {
5529        "this": True,
5530        "expression": True,
5531        "replacement": False,
5532        "position": False,
5533        "occurrence": False,
5534        "parameters": False,
5535        "modifiers": False,
5536    }
5537
5538
5539class RegexpLike(Binary, Func):
5540    arg_types = {"this": True, "expression": True, "flag": False}
5541
5542
5543class RegexpILike(Binary, Func):
5544    arg_types = {"this": True, "expression": True, "flag": False}
5545
5546
5547# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5548# limit is the number of times a pattern is applied
5549class RegexpSplit(Func):
5550    arg_types = {"this": True, "expression": True, "limit": False}
5551
5552
5553class Repeat(Func):
5554    arg_types = {"this": True, "times": True}
5555
5556
5557# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5558# tsql third argument function == trunctaion if not 0
5559class Round(Func):
5560    arg_types = {"this": True, "decimals": False, "truncate": False}
5561
5562
5563class RowNumber(Func):
5564    arg_types: t.Dict[str, t.Any] = {}
5565
5566
5567class SafeDivide(Func):
5568    arg_types = {"this": True, "expression": True}
5569
5570
5571class SHA(Func):
5572    _sql_names = ["SHA", "SHA1"]
5573
5574
5575class SHA2(Func):
5576    _sql_names = ["SHA2"]
5577    arg_types = {"this": True, "length": False}
5578
5579
5580class Sign(Func):
5581    _sql_names = ["SIGN", "SIGNUM"]
5582
5583
5584class SortArray(Func):
5585    arg_types = {"this": True, "asc": False}
5586
5587
5588class Split(Func):
5589    arg_types = {"this": True, "expression": True, "limit": False}
5590
5591
5592# Start may be omitted in the case of postgres
5593# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5594class Substring(Func):
5595    arg_types = {"this": True, "start": False, "length": False}
5596
5597
5598class StandardHash(Func):
5599    arg_types = {"this": True, "expression": False}
5600
5601
5602class StartsWith(Func):
5603    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5604    arg_types = {"this": True, "expression": True}
5605
5606
5607class StrPosition(Func):
5608    arg_types = {
5609        "this": True,
5610        "substr": True,
5611        "position": False,
5612        "instance": False,
5613    }
5614
5615
5616class StrToDate(Func):
5617    arg_types = {"this": True, "format": True}
5618
5619
5620class StrToTime(Func):
5621    arg_types = {"this": True, "format": True, "zone": False}
5622
5623
5624# Spark allows unix_timestamp()
5625# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5626class StrToUnix(Func):
5627    arg_types = {"this": False, "format": False}
5628
5629
5630# https://prestodb.io/docs/current/functions/string.html
5631# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5632class StrToMap(Func):
5633    arg_types = {
5634        "this": True,
5635        "pair_delim": False,
5636        "key_value_delim": False,
5637        "duplicate_resolution_callback": False,
5638    }
5639
5640
5641class NumberToStr(Func):
5642    arg_types = {"this": True, "format": True, "culture": False}
5643
5644
5645class FromBase(Func):
5646    arg_types = {"this": True, "expression": True}
5647
5648
5649class Struct(Func):
5650    arg_types = {"expressions": False}
5651    is_var_len_args = True
5652
5653
5654class StructExtract(Func):
5655    arg_types = {"this": True, "expression": True}
5656
5657
5658# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5659# https://docs.snowflake.com/en/sql-reference/functions/insert
5660class Stuff(Func):
5661    _sql_names = ["STUFF", "INSERT"]
5662    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5663
5664
5665class Sum(AggFunc):
5666    pass
5667
5668
5669class Sqrt(Func):
5670    pass
5671
5672
5673class Stddev(AggFunc):
5674    pass
5675
5676
5677class StddevPop(AggFunc):
5678    pass
5679
5680
5681class StddevSamp(AggFunc):
5682    pass
5683
5684
5685class TimeToStr(Func):
5686    arg_types = {"this": True, "format": True, "culture": False}
5687
5688
5689class TimeToTimeStr(Func):
5690    pass
5691
5692
5693class TimeToUnix(Func):
5694    pass
5695
5696
5697class TimeStrToDate(Func):
5698    pass
5699
5700
5701class TimeStrToTime(Func):
5702    pass
5703
5704
5705class TimeStrToUnix(Func):
5706    pass
5707
5708
5709class Trim(Func):
5710    arg_types = {
5711        "this": True,
5712        "expression": False,
5713        "position": False,
5714        "collation": False,
5715    }
5716
5717
5718class TsOrDsAdd(Func, TimeUnit):
5719    # return_type is used to correctly cast the arguments of this expression when transpiling it
5720    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5721
5722    @property
5723    def return_type(self) -> DataType:
5724        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5725
5726
5727class TsOrDsDiff(Func, TimeUnit):
5728    arg_types = {"this": True, "expression": True, "unit": False}
5729
5730
5731class TsOrDsToDateStr(Func):
5732    pass
5733
5734
5735class TsOrDsToDate(Func):
5736    arg_types = {"this": True, "format": False, "safe": False}
5737
5738
5739class TsOrDsToTime(Func):
5740    pass
5741
5742
5743class TsOrDsToTimestamp(Func):
5744    pass
5745
5746
5747class TsOrDiToDi(Func):
5748    pass
5749
5750
5751class Unhex(Func):
5752    pass
5753
5754
5755# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5756class UnixDate(Func):
5757    pass
5758
5759
5760class UnixToStr(Func):
5761    arg_types = {"this": True, "format": False}
5762
5763
5764# https://prestodb.io/docs/current/functions/datetime.html
5765# presto has weird zone/hours/minutes
5766class UnixToTime(Func):
5767    arg_types = {
5768        "this": True,
5769        "scale": False,
5770        "zone": False,
5771        "hours": False,
5772        "minutes": False,
5773        "format": False,
5774    }
5775
5776    SECONDS = Literal.number(0)
5777    DECIS = Literal.number(1)
5778    CENTIS = Literal.number(2)
5779    MILLIS = Literal.number(3)
5780    DECIMILLIS = Literal.number(4)
5781    CENTIMILLIS = Literal.number(5)
5782    MICROS = Literal.number(6)
5783    DECIMICROS = Literal.number(7)
5784    CENTIMICROS = Literal.number(8)
5785    NANOS = Literal.number(9)
5786
5787
5788class UnixToTimeStr(Func):
5789    pass
5790
5791
5792class TimestampFromParts(Func):
5793    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5794    arg_types = {
5795        "year": True,
5796        "month": True,
5797        "day": True,
5798        "hour": True,
5799        "min": True,
5800        "sec": True,
5801        "nano": False,
5802        "zone": False,
5803        "milli": False,
5804    }
5805
5806
5807class Upper(Func):
5808    _sql_names = ["UPPER", "UCASE"]
5809
5810
5811class Corr(Binary, AggFunc):
5812    pass
5813
5814
5815class Variance(AggFunc):
5816    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5817
5818
5819class VariancePop(AggFunc):
5820    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5821
5822
5823class CovarSamp(Binary, AggFunc):
5824    pass
5825
5826
5827class CovarPop(Binary, AggFunc):
5828    pass
5829
5830
5831class Week(Func):
5832    arg_types = {"this": True, "mode": False}
5833
5834
5835class XMLTable(Func):
5836    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5837
5838
5839class Year(Func):
5840    pass
5841
5842
5843class Use(Expression):
5844    arg_types = {"this": True, "kind": False}
5845
5846
5847class Merge(Expression):
5848    arg_types = {
5849        "this": True,
5850        "using": True,
5851        "on": True,
5852        "expressions": True,
5853        "with": False,
5854    }
5855
5856
5857class When(Func):
5858    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5859
5860
5861# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5862# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5863class NextValueFor(Func):
5864    arg_types = {"this": True, "order": False}
5865
5866
5867def _norm_arg(arg):
5868    return arg.lower() if type(arg) is str else arg
5869
5870
5871ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5872FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
5873
5874JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
5875
5876
5877# Helpers
5878@t.overload
5879def maybe_parse(
5880    sql_or_expression: ExpOrStr,
5881    *,
5882    into: t.Type[E],
5883    dialect: DialectType = None,
5884    prefix: t.Optional[str] = None,
5885    copy: bool = False,
5886    **opts,
5887) -> E: ...
5888
5889
5890@t.overload
5891def maybe_parse(
5892    sql_or_expression: str | E,
5893    *,
5894    into: t.Optional[IntoType] = None,
5895    dialect: DialectType = None,
5896    prefix: t.Optional[str] = None,
5897    copy: bool = False,
5898    **opts,
5899) -> E: ...
5900
5901
5902def maybe_parse(
5903    sql_or_expression: ExpOrStr,
5904    *,
5905    into: t.Optional[IntoType] = None,
5906    dialect: DialectType = None,
5907    prefix: t.Optional[str] = None,
5908    copy: bool = False,
5909    **opts,
5910) -> Expression:
5911    """Gracefully handle a possible string or expression.
5912
5913    Example:
5914        >>> maybe_parse("1")
5915        Literal(this=1, is_string=False)
5916        >>> maybe_parse(to_identifier("x"))
5917        Identifier(this=x, quoted=False)
5918
5919    Args:
5920        sql_or_expression: the SQL code string or an expression
5921        into: the SQLGlot Expression to parse into
5922        dialect: the dialect used to parse the input expressions (in the case that an
5923            input expression is a SQL string).
5924        prefix: a string to prefix the sql with before it gets parsed
5925            (automatically includes a space)
5926        copy: whether to copy the expression.
5927        **opts: other options to use to parse the input expressions (again, in the case
5928            that an input expression is a SQL string).
5929
5930    Returns:
5931        Expression: the parsed or given expression.
5932    """
5933    if isinstance(sql_or_expression, Expression):
5934        if copy:
5935            return sql_or_expression.copy()
5936        return sql_or_expression
5937
5938    if sql_or_expression is None:
5939        raise ParseError("SQL cannot be None")
5940
5941    import sqlglot
5942
5943    sql = str(sql_or_expression)
5944    if prefix:
5945        sql = f"{prefix} {sql}"
5946
5947    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5948
5949
5950@t.overload
5951def maybe_copy(instance: None, copy: bool = True) -> None: ...
5952
5953
5954@t.overload
5955def maybe_copy(instance: E, copy: bool = True) -> E: ...
5956
5957
5958def maybe_copy(instance, copy=True):
5959    return instance.copy() if copy and instance else instance
5960
5961
5962def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
5963    """Generate a textual representation of an Expression tree"""
5964    indent = "\n" + ("  " * (level + 1))
5965    delim = f",{indent}"
5966
5967    if isinstance(node, Expression):
5968        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
5969
5970        if (node.type or verbose) and not isinstance(node, DataType):
5971            args["_type"] = node.type
5972        if node.comments or verbose:
5973            args["_comments"] = node.comments
5974
5975        if verbose:
5976            args["_id"] = id(node)
5977
5978        # Inline leaves for a more compact representation
5979        if node.is_leaf():
5980            indent = ""
5981            delim = ", "
5982
5983        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
5984        return f"{node.__class__.__name__}({indent}{items})"
5985
5986    if isinstance(node, list):
5987        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
5988        items = f"{indent}{items}" if items else ""
5989        return f"[{items}]"
5990
5991    # Indent multiline strings to match the current level
5992    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
5993
5994
5995def _is_wrong_expression(expression, into):
5996    return isinstance(expression, Expression) and not isinstance(expression, into)
5997
5998
5999def _apply_builder(
6000    expression,
6001    instance,
6002    arg,
6003    copy=True,
6004    prefix=None,
6005    into=None,
6006    dialect=None,
6007    into_arg="this",
6008    **opts,
6009):
6010    if _is_wrong_expression(expression, into):
6011        expression = into(**{into_arg: expression})
6012    instance = maybe_copy(instance, copy)
6013    expression = maybe_parse(
6014        sql_or_expression=expression,
6015        prefix=prefix,
6016        into=into,
6017        dialect=dialect,
6018        **opts,
6019    )
6020    instance.set(arg, expression)
6021    return instance
6022
6023
6024def _apply_child_list_builder(
6025    *expressions,
6026    instance,
6027    arg,
6028    append=True,
6029    copy=True,
6030    prefix=None,
6031    into=None,
6032    dialect=None,
6033    properties=None,
6034    **opts,
6035):
6036    instance = maybe_copy(instance, copy)
6037    parsed = []
6038    for expression in expressions:
6039        if expression is not None:
6040            if _is_wrong_expression(expression, into):
6041                expression = into(expressions=[expression])
6042
6043            expression = maybe_parse(
6044                expression,
6045                into=into,
6046                dialect=dialect,
6047                prefix=prefix,
6048                **opts,
6049            )
6050            parsed.extend(expression.expressions)
6051
6052    existing = instance.args.get(arg)
6053    if append and existing:
6054        parsed = existing.expressions + parsed
6055
6056    child = into(expressions=parsed)
6057    for k, v in (properties or {}).items():
6058        child.set(k, v)
6059    instance.set(arg, child)
6060
6061    return instance
6062
6063
6064def _apply_list_builder(
6065    *expressions,
6066    instance,
6067    arg,
6068    append=True,
6069    copy=True,
6070    prefix=None,
6071    into=None,
6072    dialect=None,
6073    **opts,
6074):
6075    inst = maybe_copy(instance, copy)
6076
6077    expressions = [
6078        maybe_parse(
6079            sql_or_expression=expression,
6080            into=into,
6081            prefix=prefix,
6082            dialect=dialect,
6083            **opts,
6084        )
6085        for expression in expressions
6086        if expression is not None
6087    ]
6088
6089    existing_expressions = inst.args.get(arg)
6090    if append and existing_expressions:
6091        expressions = existing_expressions + expressions
6092
6093    inst.set(arg, expressions)
6094    return inst
6095
6096
6097def _apply_conjunction_builder(
6098    *expressions,
6099    instance,
6100    arg,
6101    into=None,
6102    append=True,
6103    copy=True,
6104    dialect=None,
6105    **opts,
6106):
6107    expressions = [exp for exp in expressions if exp is not None and exp != ""]
6108    if not expressions:
6109        return instance
6110
6111    inst = maybe_copy(instance, copy)
6112
6113    existing = inst.args.get(arg)
6114    if append and existing is not None:
6115        expressions = [existing.this if into else existing] + list(expressions)
6116
6117    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
6118
6119    inst.set(arg, into(this=node) if into else node)
6120    return inst
6121
6122
6123def _apply_cte_builder(
6124    instance: E,
6125    alias: ExpOrStr,
6126    as_: ExpOrStr,
6127    recursive: t.Optional[bool] = None,
6128    append: bool = True,
6129    dialect: DialectType = None,
6130    copy: bool = True,
6131    **opts,
6132) -> E:
6133    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
6134    as_expression = maybe_parse(as_, dialect=dialect, **opts)
6135    cte = CTE(this=as_expression, alias=alias_expression)
6136    return _apply_child_list_builder(
6137        cte,
6138        instance=instance,
6139        arg="with",
6140        append=append,
6141        copy=copy,
6142        into=With,
6143        properties={"recursive": recursive or False},
6144    )
6145
6146
6147def _combine(
6148    expressions: t.Sequence[t.Optional[ExpOrStr]],
6149    operator: t.Type[Connector],
6150    dialect: DialectType = None,
6151    copy: bool = True,
6152    **opts,
6153) -> Expression:
6154    conditions = [
6155        condition(expression, dialect=dialect, copy=copy, **opts)
6156        for expression in expressions
6157        if expression is not None
6158    ]
6159
6160    this, *rest = conditions
6161    if rest:
6162        this = _wrap(this, Connector)
6163    for expression in rest:
6164        this = operator(this=this, expression=_wrap(expression, Connector))
6165
6166    return this
6167
6168
6169def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
6170    return Paren(this=expression) if isinstance(expression, kind) else expression
6171
6172
6173def union(
6174    left: ExpOrStr,
6175    right: ExpOrStr,
6176    distinct: bool = True,
6177    dialect: DialectType = None,
6178    copy: bool = True,
6179    **opts,
6180) -> Union:
6181    """
6182    Initializes a syntax tree from one UNION expression.
6183
6184    Example:
6185        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
6186        'SELECT * FROM foo UNION SELECT * FROM bla'
6187
6188    Args:
6189        left: the SQL code string corresponding to the left-hand side.
6190            If an `Expression` instance is passed, it will be used as-is.
6191        right: the SQL code string corresponding to the right-hand side.
6192            If an `Expression` instance is passed, it will be used as-is.
6193        distinct: set the DISTINCT flag if and only if this is true.
6194        dialect: the dialect used to parse the input expression.
6195        copy: whether to copy the expression.
6196        opts: other options to use to parse the input expressions.
6197
6198    Returns:
6199        The new Union instance.
6200    """
6201    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6202    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6203
6204    return Union(this=left, expression=right, distinct=distinct)
6205
6206
6207def intersect(
6208    left: ExpOrStr,
6209    right: ExpOrStr,
6210    distinct: bool = True,
6211    dialect: DialectType = None,
6212    copy: bool = True,
6213    **opts,
6214) -> Intersect:
6215    """
6216    Initializes a syntax tree from one INTERSECT expression.
6217
6218    Example:
6219        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
6220        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
6221
6222    Args:
6223        left: the SQL code string corresponding to the left-hand side.
6224            If an `Expression` instance is passed, it will be used as-is.
6225        right: the SQL code string corresponding to the right-hand side.
6226            If an `Expression` instance is passed, it will be used as-is.
6227        distinct: set the DISTINCT flag if and only if this is true.
6228        dialect: the dialect used to parse the input expression.
6229        copy: whether to copy the expression.
6230        opts: other options to use to parse the input expressions.
6231
6232    Returns:
6233        The new Intersect instance.
6234    """
6235    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6236    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6237
6238    return Intersect(this=left, expression=right, distinct=distinct)
6239
6240
6241def except_(
6242    left: ExpOrStr,
6243    right: ExpOrStr,
6244    distinct: bool = True,
6245    dialect: DialectType = None,
6246    copy: bool = True,
6247    **opts,
6248) -> Except:
6249    """
6250    Initializes a syntax tree from one EXCEPT expression.
6251
6252    Example:
6253        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
6254        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
6255
6256    Args:
6257        left: the SQL code string corresponding to the left-hand side.
6258            If an `Expression` instance is passed, it will be used as-is.
6259        right: the SQL code string corresponding to the right-hand side.
6260            If an `Expression` instance is passed, it will be used as-is.
6261        distinct: set the DISTINCT flag if and only if this is true.
6262        dialect: the dialect used to parse the input expression.
6263        copy: whether to copy the expression.
6264        opts: other options to use to parse the input expressions.
6265
6266    Returns:
6267        The new Except instance.
6268    """
6269    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6270    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6271
6272    return Except(this=left, expression=right, distinct=distinct)
6273
6274
6275def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6276    """
6277    Initializes a syntax tree from one or multiple SELECT expressions.
6278
6279    Example:
6280        >>> select("col1", "col2").from_("tbl").sql()
6281        'SELECT col1, col2 FROM tbl'
6282
6283    Args:
6284        *expressions: the SQL code string to parse as the expressions of a
6285            SELECT statement. If an Expression instance is passed, this is used as-is.
6286        dialect: the dialect used to parse the input expressions (in the case that an
6287            input expression is a SQL string).
6288        **opts: other options to use to parse the input expressions (again, in the case
6289            that an input expression is a SQL string).
6290
6291    Returns:
6292        Select: the syntax tree for the SELECT statement.
6293    """
6294    return Select().select(*expressions, dialect=dialect, **opts)
6295
6296
6297def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6298    """
6299    Initializes a syntax tree from a FROM expression.
6300
6301    Example:
6302        >>> from_("tbl").select("col1", "col2").sql()
6303        'SELECT col1, col2 FROM tbl'
6304
6305    Args:
6306        *expression: the SQL code string to parse as the FROM expressions of a
6307            SELECT statement. If an Expression instance is passed, this is used as-is.
6308        dialect: the dialect used to parse the input expression (in the case that the
6309            input expression is a SQL string).
6310        **opts: other options to use to parse the input expressions (again, in the case
6311            that the input expression is a SQL string).
6312
6313    Returns:
6314        Select: the syntax tree for the SELECT statement.
6315    """
6316    return Select().from_(expression, dialect=dialect, **opts)
6317
6318
6319def update(
6320    table: str | Table,
6321    properties: dict,
6322    where: t.Optional[ExpOrStr] = None,
6323    from_: t.Optional[ExpOrStr] = None,
6324    dialect: DialectType = None,
6325    **opts,
6326) -> Update:
6327    """
6328    Creates an update statement.
6329
6330    Example:
6331        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6332        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6333
6334    Args:
6335        *properties: dictionary of properties to set which are
6336            auto converted to sql objects eg None -> NULL
6337        where: sql conditional parsed into a WHERE statement
6338        from_: sql statement parsed into a FROM statement
6339        dialect: the dialect used to parse the input expressions.
6340        **opts: other options to use to parse the input expressions.
6341
6342    Returns:
6343        Update: the syntax tree for the UPDATE statement.
6344    """
6345    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6346    update_expr.set(
6347        "expressions",
6348        [
6349            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6350            for k, v in properties.items()
6351        ],
6352    )
6353    if from_:
6354        update_expr.set(
6355            "from",
6356            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6357        )
6358    if isinstance(where, Condition):
6359        where = Where(this=where)
6360    if where:
6361        update_expr.set(
6362            "where",
6363            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6364        )
6365    return update_expr
6366
6367
6368def delete(
6369    table: ExpOrStr,
6370    where: t.Optional[ExpOrStr] = None,
6371    returning: t.Optional[ExpOrStr] = None,
6372    dialect: DialectType = None,
6373    **opts,
6374) -> Delete:
6375    """
6376    Builds a delete statement.
6377
6378    Example:
6379        >>> delete("my_table", where="id > 1").sql()
6380        'DELETE FROM my_table WHERE id > 1'
6381
6382    Args:
6383        where: sql conditional parsed into a WHERE statement
6384        returning: sql conditional parsed into a RETURNING statement
6385        dialect: the dialect used to parse the input expressions.
6386        **opts: other options to use to parse the input expressions.
6387
6388    Returns:
6389        Delete: the syntax tree for the DELETE statement.
6390    """
6391    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6392    if where:
6393        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6394    if returning:
6395        delete_expr = t.cast(
6396            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6397        )
6398    return delete_expr
6399
6400
6401def insert(
6402    expression: ExpOrStr,
6403    into: ExpOrStr,
6404    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6405    overwrite: t.Optional[bool] = None,
6406    returning: t.Optional[ExpOrStr] = None,
6407    dialect: DialectType = None,
6408    copy: bool = True,
6409    **opts,
6410) -> Insert:
6411    """
6412    Builds an INSERT statement.
6413
6414    Example:
6415        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6416        'INSERT INTO tbl VALUES (1, 2, 3)'
6417
6418    Args:
6419        expression: the sql string or expression of the INSERT statement
6420        into: the tbl to insert data to.
6421        columns: optionally the table's column names.
6422        overwrite: whether to INSERT OVERWRITE or not.
6423        returning: sql conditional parsed into a RETURNING statement
6424        dialect: the dialect used to parse the input expressions.
6425        copy: whether to copy the expression.
6426        **opts: other options to use to parse the input expressions.
6427
6428    Returns:
6429        Insert: the syntax tree for the INSERT statement.
6430    """
6431    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6432    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6433
6434    if columns:
6435        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6436
6437    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6438
6439    if returning:
6440        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6441
6442    return insert
6443
6444
6445def condition(
6446    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6447) -> Condition:
6448    """
6449    Initialize a logical condition expression.
6450
6451    Example:
6452        >>> condition("x=1").sql()
6453        'x = 1'
6454
6455        This is helpful for composing larger logical syntax trees:
6456        >>> where = condition("x=1")
6457        >>> where = where.and_("y=1")
6458        >>> Select().from_("tbl").select("*").where(where).sql()
6459        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6460
6461    Args:
6462        *expression: the SQL code string to parse.
6463            If an Expression instance is passed, this is used as-is.
6464        dialect: the dialect used to parse the input expression (in the case that the
6465            input expression is a SQL string).
6466        copy: Whether to copy `expression` (only applies to expressions).
6467        **opts: other options to use to parse the input expressions (again, in the case
6468            that the input expression is a SQL string).
6469
6470    Returns:
6471        The new Condition instance
6472    """
6473    return maybe_parse(
6474        expression,
6475        into=Condition,
6476        dialect=dialect,
6477        copy=copy,
6478        **opts,
6479    )
6480
6481
6482def and_(
6483    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6484) -> Condition:
6485    """
6486    Combine multiple conditions with an AND logical operator.
6487
6488    Example:
6489        >>> and_("x=1", and_("y=1", "z=1")).sql()
6490        'x = 1 AND (y = 1 AND z = 1)'
6491
6492    Args:
6493        *expressions: the SQL code strings to parse.
6494            If an Expression instance is passed, this is used as-is.
6495        dialect: the dialect used to parse the input expression.
6496        copy: whether to copy `expressions` (only applies to Expressions).
6497        **opts: other options to use to parse the input expressions.
6498
6499    Returns:
6500        And: the new condition
6501    """
6502    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6503
6504
6505def or_(
6506    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6507) -> Condition:
6508    """
6509    Combine multiple conditions with an OR logical operator.
6510
6511    Example:
6512        >>> or_("x=1", or_("y=1", "z=1")).sql()
6513        'x = 1 OR (y = 1 OR z = 1)'
6514
6515    Args:
6516        *expressions: the SQL code strings to parse.
6517            If an Expression instance is passed, this is used as-is.
6518        dialect: the dialect used to parse the input expression.
6519        copy: whether to copy `expressions` (only applies to Expressions).
6520        **opts: other options to use to parse the input expressions.
6521
6522    Returns:
6523        Or: the new condition
6524    """
6525    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6526
6527
6528def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6529    """
6530    Wrap a condition with a NOT operator.
6531
6532    Example:
6533        >>> not_("this_suit='black'").sql()
6534        "NOT this_suit = 'black'"
6535
6536    Args:
6537        expression: the SQL code string to parse.
6538            If an Expression instance is passed, this is used as-is.
6539        dialect: the dialect used to parse the input expression.
6540        copy: whether to copy the expression or not.
6541        **opts: other options to use to parse the input expressions.
6542
6543    Returns:
6544        The new condition.
6545    """
6546    this = condition(
6547        expression,
6548        dialect=dialect,
6549        copy=copy,
6550        **opts,
6551    )
6552    return Not(this=_wrap(this, Connector))
6553
6554
6555def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6556    """
6557    Wrap an expression in parentheses.
6558
6559    Example:
6560        >>> paren("5 + 3").sql()
6561        '(5 + 3)'
6562
6563    Args:
6564        expression: the SQL code string to parse.
6565            If an Expression instance is passed, this is used as-is.
6566        copy: whether to copy the expression or not.
6567
6568    Returns:
6569        The wrapped expression.
6570    """
6571    return Paren(this=maybe_parse(expression, copy=copy))
6572
6573
6574SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
6575
6576
6577@t.overload
6578def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None: ...
6579
6580
6581@t.overload
6582def to_identifier(
6583    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6584) -> Identifier: ...
6585
6586
6587def to_identifier(name, quoted=None, copy=True):
6588    """Builds an identifier.
6589
6590    Args:
6591        name: The name to turn into an identifier.
6592        quoted: Whether to force quote the identifier.
6593        copy: Whether to copy name if it's an Identifier.
6594
6595    Returns:
6596        The identifier ast node.
6597    """
6598
6599    if name is None:
6600        return None
6601
6602    if isinstance(name, Identifier):
6603        identifier = maybe_copy(name, copy)
6604    elif isinstance(name, str):
6605        identifier = Identifier(
6606            this=name,
6607            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6608        )
6609    else:
6610        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6611    return identifier
6612
6613
6614def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6615    """
6616    Parses a given string into an identifier.
6617
6618    Args:
6619        name: The name to parse into an identifier.
6620        dialect: The dialect to parse against.
6621
6622    Returns:
6623        The identifier ast node.
6624    """
6625    try:
6626        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6627    except ParseError:
6628        expression = to_identifier(name)
6629
6630    return expression
6631
6632
6633INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6634
6635
6636def to_interval(interval: str | Literal) -> Interval:
6637    """Builds an interval expression from a string like '1 day' or '5 months'."""
6638    if isinstance(interval, Literal):
6639        if not interval.is_string:
6640            raise ValueError("Invalid interval string.")
6641
6642        interval = interval.this
6643
6644    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6645
6646    if not interval_parts:
6647        raise ValueError("Invalid interval string.")
6648
6649    return Interval(
6650        this=Literal.string(interval_parts.group(1)),
6651        unit=Var(this=interval_parts.group(2).upper()),
6652    )
6653
6654
6655def to_table(
6656    sql_path: str | Table, dialect: DialectType = None, copy: bool = True, **kwargs
6657) -> Table:
6658    """
6659    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6660    If a table is passed in then that table is returned.
6661
6662    Args:
6663        sql_path: a `[catalog].[schema].[table]` string.
6664        dialect: the source dialect according to which the table name will be parsed.
6665        copy: Whether to copy a table if it is passed in.
6666        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6667
6668    Returns:
6669        A table expression.
6670    """
6671    if isinstance(sql_path, Table):
6672        return maybe_copy(sql_path, copy=copy)
6673
6674    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6675
6676    for k, v in kwargs.items():
6677        table.set(k, v)
6678
6679    return table
6680
6681
6682def to_column(
6683    sql_path: str | Column,
6684    quoted: t.Optional[bool] = None,
6685    dialect: DialectType = None,
6686    copy: bool = True,
6687    **kwargs,
6688) -> Column:
6689    """
6690    Create a column from a `[table].[column]` sql path. Table is optional.
6691    If a column is passed in then that column is returned.
6692
6693    Args:
6694        sql_path: a `[table].[column]` string.
6695        quoted: Whether or not to force quote identifiers.
6696        dialect: the source dialect according to which the column name will be parsed.
6697        copy: Whether to copy a column if it is passed in.
6698        kwargs: the kwargs to instantiate the resulting `Column` expression with.
6699
6700    Returns:
6701        A column expression.
6702    """
6703    if isinstance(sql_path, Column):
6704        return maybe_copy(sql_path, copy=copy)
6705
6706    try:
6707        col = maybe_parse(sql_path, into=Column, dialect=dialect)
6708    except ParseError:
6709        return column(*reversed(sql_path.split(".")), quoted=quoted, **kwargs)
6710
6711    for k, v in kwargs.items():
6712        col.set(k, v)
6713
6714    if quoted:
6715        for i in col.find_all(Identifier):
6716            i.set("quoted", True)
6717
6718    return col
6719
6720
6721def alias_(
6722    expression: ExpOrStr,
6723    alias: t.Optional[str | Identifier],
6724    table: bool | t.Sequence[str | Identifier] = False,
6725    quoted: t.Optional[bool] = None,
6726    dialect: DialectType = None,
6727    copy: bool = True,
6728    **opts,
6729):
6730    """Create an Alias expression.
6731
6732    Example:
6733        >>> alias_('foo', 'bar').sql()
6734        'foo AS bar'
6735
6736        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6737        '(SELECT 1, 2) AS bar(a, b)'
6738
6739    Args:
6740        expression: the SQL code strings to parse.
6741            If an Expression instance is passed, this is used as-is.
6742        alias: the alias name to use. If the name has
6743            special characters it is quoted.
6744        table: Whether to create a table alias, can also be a list of columns.
6745        quoted: whether to quote the alias
6746        dialect: the dialect used to parse the input expression.
6747        copy: Whether to copy the expression.
6748        **opts: other options to use to parse the input expressions.
6749
6750    Returns:
6751        Alias: the aliased expression
6752    """
6753    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6754    alias = to_identifier(alias, quoted=quoted)
6755
6756    if table:
6757        table_alias = TableAlias(this=alias)
6758        exp.set("alias", table_alias)
6759
6760        if not isinstance(table, bool):
6761            for column in table:
6762                table_alias.append("columns", to_identifier(column, quoted=quoted))
6763
6764        return exp
6765
6766    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6767    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6768    # for the complete Window expression.
6769    #
6770    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6771
6772    if "alias" in exp.arg_types and not isinstance(exp, Window):
6773        exp.set("alias", alias)
6774        return exp
6775    return Alias(this=exp, alias=alias)
6776
6777
6778def subquery(
6779    expression: ExpOrStr,
6780    alias: t.Optional[Identifier | str] = None,
6781    dialect: DialectType = None,
6782    **opts,
6783) -> Select:
6784    """
6785    Build a subquery expression that's selected from.
6786
6787    Example:
6788        >>> subquery('select x from tbl', 'bar').select('x').sql()
6789        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6790
6791    Args:
6792        expression: the SQL code strings to parse.
6793            If an Expression instance is passed, this is used as-is.
6794        alias: the alias name to use.
6795        dialect: the dialect used to parse the input expression.
6796        **opts: other options to use to parse the input expressions.
6797
6798    Returns:
6799        A new Select instance with the subquery expression included.
6800    """
6801
6802    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias, **opts)
6803    return Select().from_(expression, dialect=dialect, **opts)
6804
6805
6806@t.overload
6807def column(
6808    col: str | Identifier,
6809    table: t.Optional[str | Identifier] = None,
6810    db: t.Optional[str | Identifier] = None,
6811    catalog: t.Optional[str | Identifier] = None,
6812    *,
6813    fields: t.Collection[t.Union[str, Identifier]],
6814    quoted: t.Optional[bool] = None,
6815    copy: bool = True,
6816) -> Dot:
6817    pass
6818
6819
6820@t.overload
6821def column(
6822    col: str | Identifier,
6823    table: t.Optional[str | Identifier] = None,
6824    db: t.Optional[str | Identifier] = None,
6825    catalog: t.Optional[str | Identifier] = None,
6826    *,
6827    fields: Lit[None] = None,
6828    quoted: t.Optional[bool] = None,
6829    copy: bool = True,
6830) -> Column:
6831    pass
6832
6833
6834def column(
6835    col,
6836    table=None,
6837    db=None,
6838    catalog=None,
6839    *,
6840    fields=None,
6841    quoted=None,
6842    copy=True,
6843):
6844    """
6845    Build a Column.
6846
6847    Args:
6848        col: Column name.
6849        table: Table name.
6850        db: Database name.
6851        catalog: Catalog name.
6852        fields: Additional fields using dots.
6853        quoted: Whether to force quotes on the column's identifiers.
6854        copy: Whether to copy identifiers if passed in.
6855
6856    Returns:
6857        The new Column instance.
6858    """
6859    this = Column(
6860        this=to_identifier(col, quoted=quoted, copy=copy),
6861        table=to_identifier(table, quoted=quoted, copy=copy),
6862        db=to_identifier(db, quoted=quoted, copy=copy),
6863        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
6864    )
6865
6866    if fields:
6867        this = Dot.build(
6868            (this, *(to_identifier(field, quoted=quoted, copy=copy) for field in fields))
6869        )
6870    return this
6871
6872
6873def cast(expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, **opts) -> Cast:
6874    """Cast an expression to a data type.
6875
6876    Example:
6877        >>> cast('x + 1', 'int').sql()
6878        'CAST(x + 1 AS INT)'
6879
6880    Args:
6881        expression: The expression to cast.
6882        to: The datatype to cast to.
6883        copy: Whether to copy the supplied expressions.
6884
6885    Returns:
6886        The new Cast instance.
6887    """
6888    expr = maybe_parse(expression, copy=copy, **opts)
6889    data_type = DataType.build(to, copy=copy, **opts)
6890
6891    if expr.is_type(data_type):
6892        return expr
6893
6894    expr = Cast(this=expr, to=data_type)
6895    expr.type = data_type
6896
6897    return expr
6898
6899
6900def table_(
6901    table: Identifier | str,
6902    db: t.Optional[Identifier | str] = None,
6903    catalog: t.Optional[Identifier | str] = None,
6904    quoted: t.Optional[bool] = None,
6905    alias: t.Optional[Identifier | str] = None,
6906) -> Table:
6907    """Build a Table.
6908
6909    Args:
6910        table: Table name.
6911        db: Database name.
6912        catalog: Catalog name.
6913        quote: Whether to force quotes on the table's identifiers.
6914        alias: Table's alias.
6915
6916    Returns:
6917        The new Table instance.
6918    """
6919    return Table(
6920        this=to_identifier(table, quoted=quoted) if table else None,
6921        db=to_identifier(db, quoted=quoted) if db else None,
6922        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6923        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6924    )
6925
6926
6927def values(
6928    values: t.Iterable[t.Tuple[t.Any, ...]],
6929    alias: t.Optional[str] = None,
6930    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6931) -> Values:
6932    """Build VALUES statement.
6933
6934    Example:
6935        >>> values([(1, '2')]).sql()
6936        "VALUES (1, '2')"
6937
6938    Args:
6939        values: values statements that will be converted to SQL
6940        alias: optional alias
6941        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6942         If either are provided then an alias is also required.
6943
6944    Returns:
6945        Values: the Values expression object
6946    """
6947    if columns and not alias:
6948        raise ValueError("Alias is required when providing columns")
6949
6950    return Values(
6951        expressions=[convert(tup) for tup in values],
6952        alias=(
6953            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6954            if columns
6955            else (TableAlias(this=to_identifier(alias)) if alias else None)
6956        ),
6957    )
6958
6959
6960def var(name: t.Optional[ExpOrStr]) -> Var:
6961    """Build a SQL variable.
6962
6963    Example:
6964        >>> repr(var('x'))
6965        'Var(this=x)'
6966
6967        >>> repr(var(column('x', table='y')))
6968        'Var(this=x)'
6969
6970    Args:
6971        name: The name of the var or an expression who's name will become the var.
6972
6973    Returns:
6974        The new variable node.
6975    """
6976    if not name:
6977        raise ValueError("Cannot convert empty name into var.")
6978
6979    if isinstance(name, Expression):
6980        name = name.name
6981    return Var(this=name)
6982
6983
6984def rename_table(
6985    old_name: str | Table,
6986    new_name: str | Table,
6987    dialect: DialectType = None,
6988) -> AlterTable:
6989    """Build ALTER TABLE... RENAME... expression
6990
6991    Args:
6992        old_name: The old name of the table
6993        new_name: The new name of the table
6994        dialect: The dialect to parse the table.
6995
6996    Returns:
6997        Alter table expression
6998    """
6999    old_table = to_table(old_name, dialect=dialect)
7000    new_table = to_table(new_name, dialect=dialect)
7001    return AlterTable(
7002        this=old_table,
7003        actions=[
7004            RenameTable(this=new_table),
7005        ],
7006    )
7007
7008
7009def rename_column(
7010    table_name: str | Table,
7011    old_column_name: str | Column,
7012    new_column_name: str | Column,
7013    exists: t.Optional[bool] = None,
7014    dialect: DialectType = None,
7015) -> AlterTable:
7016    """Build ALTER TABLE... RENAME COLUMN... expression
7017
7018    Args:
7019        table_name: Name of the table
7020        old_column: The old name of the column
7021        new_column: The new name of the column
7022        exists: Whether to add the `IF EXISTS` clause
7023        dialect: The dialect to parse the table/column.
7024
7025    Returns:
7026        Alter table expression
7027    """
7028    table = to_table(table_name, dialect=dialect)
7029    old_column = to_column(old_column_name, dialect=dialect)
7030    new_column = to_column(new_column_name, dialect=dialect)
7031    return AlterTable(
7032        this=table,
7033        actions=[
7034            RenameColumn(this=old_column, to=new_column, exists=exists),
7035        ],
7036    )
7037
7038
7039def convert(value: t.Any, copy: bool = False) -> Expression:
7040    """Convert a python value into an expression object.
7041
7042    Raises an error if a conversion is not possible.
7043
7044    Args:
7045        value: A python object.
7046        copy: Whether to copy `value` (only applies to Expressions and collections).
7047
7048    Returns:
7049        The equivalent expression object.
7050    """
7051    if isinstance(value, Expression):
7052        return maybe_copy(value, copy)
7053    if isinstance(value, str):
7054        return Literal.string(value)
7055    if isinstance(value, bool):
7056        return Boolean(this=value)
7057    if value is None or (isinstance(value, float) and math.isnan(value)):
7058        return null()
7059    if isinstance(value, numbers.Number):
7060        return Literal.number(value)
7061    if isinstance(value, bytes):
7062        return HexString(this=value.hex())
7063    if isinstance(value, datetime.datetime):
7064        datetime_literal = Literal.string(
7065            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat(
7066                sep=" "
7067            )
7068        )
7069        return TimeStrToTime(this=datetime_literal)
7070    if isinstance(value, datetime.date):
7071        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
7072        return DateStrToDate(this=date_literal)
7073    if isinstance(value, tuple):
7074        if hasattr(value, "_fields"):
7075            return Struct(
7076                expressions=[
7077                    PropertyEQ(
7078                        this=to_identifier(k), expression=convert(getattr(value, k), copy=copy)
7079                    )
7080                    for k in value._fields
7081                ]
7082            )
7083        return Tuple(expressions=[convert(v, copy=copy) for v in value])
7084    if isinstance(value, list):
7085        return Array(expressions=[convert(v, copy=copy) for v in value])
7086    if isinstance(value, dict):
7087        return Map(
7088            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
7089            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
7090        )
7091    if hasattr(value, "__dict__"):
7092        return Struct(
7093            expressions=[
7094                PropertyEQ(this=to_identifier(k), expression=convert(v, copy=copy))
7095                for k, v in value.__dict__.items()
7096            ]
7097        )
7098    raise ValueError(f"Cannot convert {value}")
7099
7100
7101def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
7102    """
7103    Replace children of an expression with the result of a lambda fun(child) -> exp.
7104    """
7105    for k, v in tuple(expression.args.items()):
7106        is_list_arg = type(v) is list
7107
7108        child_nodes = v if is_list_arg else [v]
7109        new_child_nodes = []
7110
7111        for cn in child_nodes:
7112            if isinstance(cn, Expression):
7113                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
7114                    new_child_nodes.append(child_node)
7115            else:
7116                new_child_nodes.append(cn)
7117
7118        expression.set(k, new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0))
7119
7120
7121def replace_tree(
7122    expression: Expression,
7123    fun: t.Callable,
7124    prune: t.Optional[t.Callable[[Expression], bool]] = None,
7125) -> Expression:
7126    """
7127    Replace an entire tree with the result of function calls on each node.
7128
7129    This will be traversed in reverse dfs, so leaves first.
7130    If new nodes are created as a result of function calls, they will also be traversed.
7131    """
7132    stack = list(expression.dfs(prune=prune))
7133
7134    while stack:
7135        node = stack.pop()
7136        new_node = fun(node)
7137
7138        if new_node is not node:
7139            node.replace(new_node)
7140
7141            if isinstance(new_node, Expression):
7142                stack.append(new_node)
7143
7144    return new_node
7145
7146
7147def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
7148    """
7149    Return all table names referenced through columns in an expression.
7150
7151    Example:
7152        >>> import sqlglot
7153        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
7154        ['a', 'c']
7155
7156    Args:
7157        expression: expression to find table names.
7158        exclude: a table name to exclude
7159
7160    Returns:
7161        A list of unique names.
7162    """
7163    return {
7164        table
7165        for table in (column.table for column in expression.find_all(Column))
7166        if table and table != exclude
7167    }
7168
7169
7170def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
7171    """Get the full name of a table as a string.
7172
7173    Args:
7174        table: Table expression node or string.
7175        dialect: The dialect to generate the table name for.
7176        identify: Determines when an identifier should be quoted. Possible values are:
7177            False (default): Never quote, except in cases where it's mandatory by the dialect.
7178            True: Always quote.
7179
7180    Examples:
7181        >>> from sqlglot import exp, parse_one
7182        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
7183        'a.b.c'
7184
7185    Returns:
7186        The table name.
7187    """
7188
7189    table = maybe_parse(table, into=Table, dialect=dialect)
7190
7191    if not table:
7192        raise ValueError(f"Cannot parse {table}")
7193
7194    return ".".join(
7195        (
7196            part.sql(dialect=dialect, identify=True, copy=False)
7197            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
7198            else part.name
7199        )
7200        for part in table.parts
7201    )
7202
7203
7204def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
7205    """Returns a case normalized table name without quotes.
7206
7207    Args:
7208        table: the table to normalize
7209        dialect: the dialect to use for normalization rules
7210        copy: whether to copy the expression.
7211
7212    Examples:
7213        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
7214        'A-B.c'
7215    """
7216    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
7217
7218    return ".".join(
7219        p.name
7220        for p in normalize_identifiers(
7221            to_table(table, dialect=dialect, copy=copy), dialect=dialect
7222        ).parts
7223    )
7224
7225
7226def replace_tables(
7227    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
7228) -> E:
7229    """Replace all tables in expression according to the mapping.
7230
7231    Args:
7232        expression: expression node to be transformed and replaced.
7233        mapping: mapping of table names.
7234        dialect: the dialect of the mapping table
7235        copy: whether to copy the expression.
7236
7237    Examples:
7238        >>> from sqlglot import exp, parse_one
7239        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
7240        'SELECT * FROM c /* a.b */'
7241
7242    Returns:
7243        The mapped expression.
7244    """
7245
7246    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
7247
7248    def _replace_tables(node: Expression) -> Expression:
7249        if isinstance(node, Table):
7250            original = normalize_table_name(node, dialect=dialect)
7251            new_name = mapping.get(original)
7252
7253            if new_name:
7254                table = to_table(
7255                    new_name,
7256                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
7257                    dialect=dialect,
7258                )
7259                table.add_comments([original])
7260                return table
7261        return node
7262
7263    return expression.transform(_replace_tables, copy=copy)  # type: ignore
7264
7265
7266def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
7267    """Replace placeholders in an expression.
7268
7269    Args:
7270        expression: expression node to be transformed and replaced.
7271        args: positional names that will substitute unnamed placeholders in the given order.
7272        kwargs: keyword arguments that will substitute named placeholders.
7273
7274    Examples:
7275        >>> from sqlglot import exp, parse_one
7276        >>> replace_placeholders(
7277        ...     parse_one("select * from :tbl where ? = ?"),
7278        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
7279        ... ).sql()
7280        "SELECT * FROM foo WHERE str_col = 'b'"
7281
7282    Returns:
7283        The mapped expression.
7284    """
7285
7286    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
7287        if isinstance(node, Placeholder):
7288            if node.this:
7289                new_name = kwargs.get(node.this)
7290                if new_name is not None:
7291                    return convert(new_name)
7292            else:
7293                try:
7294                    return convert(next(args))
7295                except StopIteration:
7296                    pass
7297        return node
7298
7299    return expression.transform(_replace_placeholders, iter(args), **kwargs)
7300
7301
7302def expand(
7303    expression: Expression,
7304    sources: t.Dict[str, Query],
7305    dialect: DialectType = None,
7306    copy: bool = True,
7307) -> Expression:
7308    """Transforms an expression by expanding all referenced sources into subqueries.
7309
7310    Examples:
7311        >>> from sqlglot import parse_one
7312        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
7313        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
7314
7315        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
7316        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
7317
7318    Args:
7319        expression: The expression to expand.
7320        sources: A dictionary of name to Queries.
7321        dialect: The dialect of the sources dict.
7322        copy: Whether to copy the expression during transformation. Defaults to True.
7323
7324    Returns:
7325        The transformed expression.
7326    """
7327    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
7328
7329    def _expand(node: Expression):
7330        if isinstance(node, Table):
7331            name = normalize_table_name(node, dialect=dialect)
7332            source = sources.get(name)
7333            if source:
7334                subquery = source.subquery(node.alias or name)
7335                subquery.comments = [f"source: {name}"]
7336                return subquery.transform(_expand, copy=False)
7337        return node
7338
7339    return expression.transform(_expand, copy=copy)
7340
7341
7342def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
7343    """
7344    Returns a Func expression.
7345
7346    Examples:
7347        >>> func("abs", 5).sql()
7348        'ABS(5)'
7349
7350        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
7351        'CAST(5 AS DOUBLE)'
7352
7353    Args:
7354        name: the name of the function to build.
7355        args: the args used to instantiate the function of interest.
7356        copy: whether to copy the argument expressions.
7357        dialect: the source dialect.
7358        kwargs: the kwargs used to instantiate the function of interest.
7359
7360    Note:
7361        The arguments `args` and `kwargs` are mutually exclusive.
7362
7363    Returns:
7364        An instance of the function of interest, or an anonymous function, if `name` doesn't
7365        correspond to an existing `sqlglot.expressions.Func` class.
7366    """
7367    if args and kwargs:
7368        raise ValueError("Can't use both args and kwargs to instantiate a function.")
7369
7370    from sqlglot.dialects.dialect import Dialect
7371
7372    dialect = Dialect.get_or_raise(dialect)
7373
7374    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
7375    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
7376
7377    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7378    if constructor:
7379        if converted:
7380            if "dialect" in constructor.__code__.co_varnames:
7381                function = constructor(converted, dialect=dialect)
7382            else:
7383                function = constructor(converted)
7384        elif constructor.__name__ == "from_arg_list":
7385            function = constructor.__self__(**kwargs)  # type: ignore
7386        else:
7387            constructor = FUNCTION_BY_NAME.get(name.upper())
7388            if constructor:
7389                function = constructor(**kwargs)
7390            else:
7391                raise ValueError(
7392                    f"Unable to convert '{name}' into a Func. Either manually construct "
7393                    "the Func expression of interest or parse the function call."
7394                )
7395    else:
7396        kwargs = kwargs or {"expressions": converted}
7397        function = Anonymous(this=name, **kwargs)
7398
7399    for error_message in function.error_messages(converted):
7400        raise ValueError(error_message)
7401
7402    return function
7403
7404
7405def case(
7406    expression: t.Optional[ExpOrStr] = None,
7407    **opts,
7408) -> Case:
7409    """
7410    Initialize a CASE statement.
7411
7412    Example:
7413        case().when("a = 1", "foo").else_("bar")
7414
7415    Args:
7416        expression: Optionally, the input expression (not all dialects support this)
7417        **opts: Extra keyword arguments for parsing `expression`
7418    """
7419    if expression is not None:
7420        this = maybe_parse(expression, **opts)
7421    else:
7422        this = None
7423    return Case(this=this, ifs=[])
7424
7425
7426def array(
7427    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7428) -> Array:
7429    """
7430    Returns an array.
7431
7432    Examples:
7433        >>> array(1, 'x').sql()
7434        'ARRAY(1, x)'
7435
7436    Args:
7437        expressions: the expressions to add to the array.
7438        copy: whether to copy the argument expressions.
7439        dialect: the source dialect.
7440        kwargs: the kwargs used to instantiate the function of interest.
7441
7442    Returns:
7443        An array expression.
7444    """
7445    return Array(
7446        expressions=[
7447            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7448            for expression in expressions
7449        ]
7450    )
7451
7452
7453def tuple_(
7454    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7455) -> Tuple:
7456    """
7457    Returns an tuple.
7458
7459    Examples:
7460        >>> tuple_(1, 'x').sql()
7461        '(1, x)'
7462
7463    Args:
7464        expressions: the expressions to add to the tuple.
7465        copy: whether to copy the argument expressions.
7466        dialect: the source dialect.
7467        kwargs: the kwargs used to instantiate the function of interest.
7468
7469    Returns:
7470        A tuple expression.
7471    """
7472    return Tuple(
7473        expressions=[
7474            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7475            for expression in expressions
7476        ]
7477    )
7478
7479
7480def true() -> Boolean:
7481    """
7482    Returns a true Boolean expression.
7483    """
7484    return Boolean(this=True)
7485
7486
7487def false() -> Boolean:
7488    """
7489    Returns a false Boolean expression.
7490    """
7491    return Boolean(this=False)
7492
7493
7494def null() -> Null:
7495    """
7496    Returns a Null expression.
7497    """
7498    return Null()
7499
7500
7501NONNULL_CONSTANTS = (
7502    Literal,
7503    Boolean,
7504)
7505
7506CONSTANTS = (
7507    Literal,
7508    Boolean,
7509    Null,
7510)